﻿using BonjourQuotation.DataAccess.Entity_Framework;
using BonjourQuotation.ObjectModel.BusinessData;
using BonjourQuotation.ObjectModel.ErrorData;
using BonjourQuotation.ObjectModel.LoggerData;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using BonjourQuotation.ObjectModel.StringHelper;
using System.Data;

namespace BonjourQuotation.DataAccess.Repositories
{
    public class QuotationRepository : IQuotationRepository
    {
        private readonly BonDataEntities _context = new BonDataEntities();
        private readonly log4net.ILog logger = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        public string test()
        {
            string mes = "ok";
            try
            {
                var dd = _context.tblQuotaHotels.Where(o => o.QuotaID == 5115).SingleOrDefault();
            }
            catch(Exception ex)
            {
                mes = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return mes;
        }
        public int GetQuotationQuotaID(int param, List<string> UserRegions)
        {
            try
            {
                if (param == int.MaxValue)
                    return _context.tblQuotations.Where(Quota => Quota.Active == true && UserRegions.Contains(Quota.Region)).Max(obj => obj.QuotaID);
                if (param == int.MinValue)
                    return _context.tblQuotations.Where(Quota => Quota.Active == true && UserRegions.Contains(Quota.Region)).Min(obj => obj.QuotaID);

                return _context.tblQuotations.Where(obj => obj.QuotaID == param && obj.Active == true && UserRegions.Contains(obj.Region)).SingleOrDefault().QuotaID;
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToMessageAndCompleteStacktrace());
                return 5115;
            }
        }
        public List<QuotaLookupBData> LoadQuotaLookupData(string ByRef, string Keyword)
        {
            List<QuotaLookupBData> LookupData = null;
            if(ByRef == "ID")
            {
                int id=0;
                try
                {
                    id = Convert.ToInt32(Keyword);
                }
                catch (Exception ex)
                {
                    logger.Error(ex.ToMessageAndCompleteStacktrace());
                }
                LookupData = (from quota in _context.tblQuotations where quota.QuotaID == id && quota.Active == true
                             select new QuotaLookupBData()
                             {QuotaID = quota.QuotaID, QuotaCustomer = (from cust in _context.tblCustomers where cust.CustomerID == quota.CustomerID select cust.OrganizationName).FirstOrDefault(),
                             QuotaName = quota.QuotaName, ArrivalDate = quota.ArrivalDate,
                              DepartureDate = quota.DepartureDate
                             }).Take(10).ToList();
            }
            if (ByRef == "Ref")
            {
                LookupData = (from quota in _context.tblQuotations
                              where quota.QuotaName.Contains(Keyword) && quota.Active == true
                              select new QuotaLookupBData()
                              {
                                  QuotaID = quota.QuotaID,
                                  QuotaCustomer = (from cust in _context.tblCustomers where cust.CustomerID == quota.CustomerID && cust.Active == true select cust.OrganizationName).FirstOrDefault(),
                                  QuotaName = quota.QuotaName,
                                  ArrivalDate = quota.ArrivalDate,
                                  DepartureDate = quota.DepartureDate
                              }).Take(10).ToList();
            }
            if (ByRef == "Agency")
            {
                LookupData = (from quota in _context.tblQuotations
                              where quota.QuotaCode.Contains(Keyword) && quota.Active == true
                              select new QuotaLookupBData()
                              {
                                  QuotaID = quota.QuotaID,
                                  QuotaCustomer = (from cust in _context.tblCustomers where cust.CustomerID == quota.CustomerID && cust.Active == true select cust.OrganizationName).FirstOrDefault(),
                                  QuotaName = quota.QuotaName,
                                  ArrivalDate = quota.ArrivalDate,
                                  DepartureDate = quota.DepartureDate
                              }).Take(10).ToList();
            }
            if (ByRef == "Cust")
            {
                LookupData = (from quota in _context.tblQuotations
                              from cust in _context.tblCustomers
                              where cust.CustomerID == quota.CustomerID && cust.OrganizationName.Contains(Keyword) && quota.Active == true && cust.Active == true
                              select new QuotaLookupBData()
                              {
                                  QuotaID = quota.QuotaID,
                                  QuotaCustomer = cust.OrganizationName,
                                  QuotaName = quota.QuotaName,
                                  ArrivalDate = quota.ArrivalDate,
                                  DepartureDate = quota.DepartureDate
                              }).Take(10).ToList();
            }     
            return LookupData;
        }
        public ErrorBInfo MakeQuotaToId(int id)
        {
            ErrorBInfo error = new ErrorBInfo();
            tblQuotation Quota = new tblQuotation() { QuotaCode = "tt", QuotaDate = DateTime.Now, LastUpDate = DateTime.Now };

            try
            {
                while (Quota.QuotaID < id)
                {
                    Quota = new tblQuotation() { QuotaCode = "tt", QuotaDate = DateTime.Now, LastUpDate = DateTime.Now };
                    _context.tblQuotations.Add(Quota);
                    _context.SaveChanges();
                }
            }
            catch(Exception ex)
            {
                error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return error;
        }
        public QuotationDetailsBData GetQuotationBDataByQuotaId(int QuotaId, string UserName)
        {
            QuotationDetailsBData QuotaBData = null;
            try
            {
                QuotaBData = (from quota in _context.tblQuotations
                              where quota.QuotaID == QuotaId && quota.Active == true
                              select new QuotationDetailsBData()
                              {
                                  QuotaID = quota.QuotaID,
                                  Accepted = quota.Accepted,
                                  IsGir = quota.IsGir,
                                  SelectedQuotaCountry = quota.QuotaType,
                                  NoOfFlights = quota.NoOfFlights,
                                  SelectedRegion = quota.Region,
                                  QuotaCode = quota.QuotaCode,
                                  SelectedCustomerId = quota.CustomerID,
                                  SelectedSeason = quota.SeasonID,
                                  QuotaName = quota.QuotaName,
                                  QuotaNote = quota.QuotaNote,
                                  SelectedEmployeeId = quota.EmployeeID,
                                  NoNights = quota.NoOfNights,
                                  NoOfMeals = quota.NoOfMeals,
                                  ArrivalDate = quota.ArrivalDate,
                                  SelectedArrivalCity = quota.ArrivalCity,
                                  QuotaDate = quota.QuotaDate,
                                  LastUpDate = quota.LastUpDate,
                                  MaxNoOfPax = quota.MaxNoOfPax,
                                  MinNoOfPax = quota.MinNoOfPax,
                                  IncNoOfPax = quota.IncNoOfPax,
                                  HFreeRoom = quota.HFreeRoom,
                                  HForPaid = quota.HForPaid,
                                  DepartureDate = quota.DepartureDate,
                                  SelectedDepartureCity = quota.DepartureCity,
                                  SelectedFreeCalcType = quota.FreeCalcType,
                                  profit = quota.Profit,
                                  OFreeService = quota.OFreeService,
                                  OForPaid = quota.OForPaid,
                                  Proposal = quota.ProposalWordDoc,
                              }).SingleOrDefault();
            }
            
            catch (Exception ex)
            {
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            if (QuotaBData != null)
            {
                // group Info
                var GroupInfo = _context.tblGroups.Where(gr => gr.QuotaID == QuotaId).Select(gr => new { GrId = gr.GroupID, GrCode = gr.GroupCode, GrName = gr.GroupName }).FirstOrDefault();
                if(GroupInfo != null)
                {
                    QuotaBData.GroupID = GroupInfo.GrId;
                    QuotaBData.GroupCode = GroupInfo.GrCode;
                    QuotaBData.GroupName = GroupInfo.GrName;
                }
                // country
                QuotaBData.QuotaCountry = (from ctr in _context.tblCountries
                                           where ctr.Active == true
                                           orderby ctr.OrderNum, ctr.CountryName
                                           select new
                                           {
                                               Key = ctr.CountryID,
                                               Value = ctr.CountryName,
                                           }).ToDictionary(o => o.Key, o => o.Value);
                // region
                QuotaBData.Region = (from reg in _context.tblQuotaRegions
                                         where reg.Active == true
                                         orderby reg.Region
                                         select new
                                         {
                                             Key = reg.RegionID,
                                             Value = reg.Region,
                                         }).ToDictionary(o => o.Key, o => o.Value);
                // customer
                QuotaBData.CustomerId = (from cust in _context.tblCustomers
                                         where cust.Active == true
                                         orderby cust.OrganizationName, cust.CustomerCode
                                         select new
                                         {
                                             Key = cust.CustomerID,
                                             Value = cust.OrganizationName,
                                         }).ToDictionary(o => o.Key, o => o.Value);

                // Season
                QuotaBData.Season = (from season in _context.tblSeasons
                                     orderby season.IsCurrent descending
                                     select new
                                     {
                                         Key = season.SeasonID,
                                         Value = season.SeasonDescription,
                                     }).ToDictionary(o => o.Key, o => o.Value);
                // Employee
               // var EmpID = _context.Users.Where(usr => usr.UserName == UserName).Select(usr => usr.EmployeeID).SingleOrDefault();
                QuotaBData.Employee = (from user in _context.Users
                                       from emp in _context.tblEmployees
                                      // from SupEmp in _context.tblSupEmployees
                                     //  where user.EmployeeID == emp.EmployeeID && emp.Active == true && (user.EmployeeID == EmpID || emp.SupervisorID == EmpID)
                                       where user.EmployeeID == emp.EmployeeID && emp.Active == true 
                                       orderby emp.FirstName, emp.LastName
                                       select new
                                       {
                                           Key = emp.EmployeeID,
                                           Value = emp.FirstName + " " + emp.LastName,
                                       }).ToDictionary(o => o.Key, o => o.Value);

                // arrival city
                QuotaBData.ArrivalCity = new Dictionary<int, string>();
                QuotaBData.DepartureCity = new Dictionary<int, string>();
                var Cities = _context.tblQuotaCities.Where(city => city.Active == true).Select(obj => new { CId = obj.CityID, city = obj.City }).OrderBy(obj => obj.city).ToList();
                foreach (var city in Cities)
                {
                    QuotaBData.ArrivalCity.Add(city.CId, city.city);
                    QuotaBData.DepartureCity.Add(city.CId, city.city);
                }
            }
            return QuotaBData;
        }
        public QuotationDetailsBData SaveAsQuotationBDataByQuotaId(int QuotaId, string UserName)
        {
            QuotationDetailsBData QuotaBData = new QuotationDetailsBData();
            ErrorBInfo Error = new ErrorBInfo();
            tblQuotation NewQuota = null;
            using (var transaction = _context.Database.BeginTransaction(IsolationLevel.Serializable))
            {
                var OldQuotaEntity = _context.tblQuotations.Where(quota => quota.QuotaID == QuotaId && quota.Active == true).SingleOrDefault();
                if (OldQuotaEntity != null)
                {
                    NewQuota = new tblQuotation()
                    {
                        IsGir = OldQuotaEntity.IsGir,
                        Accepted = false,
                        Active = OldQuotaEntity.Active,
                        ArrivalCity = OldQuotaEntity.ArrivalCity,
                        ArrivalDate = OldQuotaEntity.ArrivalDate,
                        CustomerID = OldQuotaEntity.CustomerID,
                        DepartureCity = OldQuotaEntity.DepartureCity,
                        DepartureDate = OldQuotaEntity.DepartureDate,
                        DriverFlag = OldQuotaEntity.DriverFlag,
                        EmployeeID = OldQuotaEntity.EmployeeID,
                        FreeCalcType = OldQuotaEntity.FreeCalcType,
                        FreeFlag = OldQuotaEntity.FreeFlag,
                        GuideFlag = OldQuotaEntity.GuideFlag,
                        HForPaid = OldQuotaEntity.HForPaid,
                        HFreeRoom = OldQuotaEntity.HFreeRoom,
                        IncNoOfPax = OldQuotaEntity.IncNoOfPax,
                        LastUpDate = OldQuotaEntity.LastUpDate,
                        MaxNoOfPax = OldQuotaEntity.MaxNoOfPax,
                        MinNoOfPax = OldQuotaEntity.MinNoOfPax,
                        NoOfFlights = OldQuotaEntity.NoOfFlights,
                        NoOfNights = OldQuotaEntity.NoOfNights,
                        OForPaid = OldQuotaEntity.OForPaid,
                        OFreeService = OldQuotaEntity.OFreeService,
                        Profit = OldQuotaEntity.Profit,
                        ProposalWordDoc = OldQuotaEntity.ProposalWordDoc,
                        QuotaCode = OldQuotaEntity.QuotaCode,
                        QuotaNote = OldQuotaEntity.QuotaNote,
                        QuotaDate = OldQuotaEntity.QuotaDate,
                        QuotaName = OldQuotaEntity.QuotaName + string.Format("({0})", OldQuotaEntity.QuotaID),
                        QuotaType = OldQuotaEntity.QuotaType,
                        Region = OldQuotaEntity.Region,
                        SeasonID = OldQuotaEntity.SeasonID,
                        NoOfMeals = OldQuotaEntity.NoOfMeals,
                    };
 
                        try
                        {
                            _context.tblQuotations.Add(NewQuota);
                            _context.SaveChanges();
                            var tblQuotaBus = _context.tblQuotaBus.Where(bus => bus.QuotaID == QuotaId).ToList();
                            var tblQuotaCalcs = _context.tblQuotaCalcs.Where(calc => calc.QuotaID == QuotaId).ToList();
                            var tblQuotaEntrances = _context.tblQuotaEntrances.Where(entrance => entrance.QuotaID == QuotaId).ToList();
                            var tblQuotaEntrGrps = _context.tblQuotaEntrGrps.Where(grp => grp.QuotaID == QuotaId).ToList();
                            var tblQuotaGuides = _context.tblQuotaGuides.Where(guide => guide.QuotaID == QuotaId).ToList();
                            var tblQuotaHotels = _context.tblQuotaHotels.Where(htl => htl.QuotaID == QuotaId).ToList();
                            var tblQuotaLunDins = _context.tblQuotaLunDins.Where(lunch => lunch.QuotaID == QuotaId).ToList();
                            foreach (var itm in tblQuotaHotels)
                            {
                                var Hotel = new tblQuotaHotel()
                                {
                                    QuotaID = NewQuota.QuotaID,
                                    BFreeFlag = itm.BFreeFlag,
                                    BGuideFlag = itm.BGuideFlag,
                                    Breakfast = itm.Breakfast,
                                    BrfType = itm.BrfType,
                                    City = itm.City,
                                    Description = itm.Description,
                                    DriverFlag = itm.DriverFlag,
                                    FreeFlag = itm.FreeFlag,
                                    GuideFlag = itm.GuideFlag,
                                    LineNo = itm.LineNo,
                                    Luggage = itm.Luggage,
                                    Nights = itm.Nights,
                                    OccTax = itm.OccTax,
                                    Room = itm.Room,
                                    StateID = itm.StateID,
                                    Tax = itm.Tax,
                                    VendorID = itm.VendorID,
                                    Currency = itm.Currency,    
                                    HotelMFlag = itm.HotelMFlag,
                                };
                                _context.tblQuotaHotels.Add(Hotel);
                            }
                            foreach (var itm in tblQuotaLunDins)
                            {
                                var din = new tblQuotaLunDin()
                                {
                                    QuotaID = NewQuota.QuotaID,       
                                    City = itm.City,               
                                    FreeFlag = itm.FreeFlag,
                                    GuideFlag = itm.GuideFlag,
                                    LineNo = itm.LineNo,    
                                    StateID = itm.StateID,  
                                    VendorID = itm.VendorID,
                                    Cost = itm.Cost,
                                    CostD = itm.CostD,
                                    CouponFlag = itm.CouponFlag,
                                    Day = itm.Day,
                                    Description = itm.Description,
                                    DescriptionD = itm.DescriptionD,
                                    FreeFlagD = itm.FreeFlagD,
                                    GuideFlagD = itm.GuideFlagD,
                                    HotelFlag = itm.HotelFlag,
                                    LunDin = itm.LunDin,
                                    ServiceID = itm.ServiceID,
                                    Currency = itm.Currency,
                                    DVendorID = itm.DVendorID,
                                    LunchMFlag = itm.LunchMFlag,
                                    DinnerMFlag = itm.DinnerMFlag,
                                };
                                _context.tblQuotaLunDins.Add(din);
                            }
                            foreach (var itm in tblQuotaEntrances)
                            {
                                var Ent = new tblQuotaEntrance()
                                {
                                    QuotaID = NewQuota.QuotaID,
                                    City = itm.City,
                                    FreeFlag = itm.FreeFlag,
                                    GuideFlag = itm.GuideFlag,
                                    LineNo = itm.LineNo,
                                    StateID = itm.StateID,
                                    VendorID = itm.VendorID,
                                    Cost = itm.Cost,                              
                                    Day = itm.Day,
                                    Description = itm.Description, 
                                    CostG = itm.CostG,
                                    Currency = itm.Currency,  
                                    EntranceMFlag = itm.EntranceMFlag,
                                };
                                _context.tblQuotaEntrances.Add(Ent);
                            }
                            foreach (var itm in tblQuotaEntrGrps)
                            {
                                var grp = new tblQuotaEntrGrp()
                                {
                                    QuotaID = NewQuota.QuotaID,
                                    City = itm.City,     
                                    LineNo = itm.LineNo,
                                    StateID = itm.StateID,
                                    VendorID = itm.VendorID,
                                    Cost = itm.Cost,
                                    Day = itm.Day,
                                    Description = itm.Description,
                                    CostG = itm.CostG,  
                                    Currency = itm.Currency,    
                                    PlaneFlag = itm.PlaneFlag,
                                };
                                _context.tblQuotaEntrGrps.Add(grp);
                            }
                            foreach (var itm in tblQuotaGuides)
                            {
                                var guide = new tblQuotaGuide()
                                {
                                    QuotaID = NewQuota.QuotaID,
                                    Qty = itm.Qty,
                                    LineNo = itm.LineNo,
                                    Rate = itm.Rate,
                                    Cost = itm.Cost,                    
                                    Description = itm.Description,
                                    Currency = itm.Currency,
                                    Day = itm.Day,
                                    DayQty = itm.DayQty,
                                    GuideID = itm.GuideID,  
                                    GuideMFlag = itm.GuideMFlag,
                                    ServiceID = itm.ServiceID,
                                };
                                _context.tblQuotaGuides.Add(guide);
                            }
                            foreach (var itm in tblQuotaBus)
                            {
                                var bus = new tblQuotaBu()
                                {
                                    QuotaID = NewQuota.QuotaID,
                                    Qty = itm.Qty,
                                    LineNo = itm.LineNo,
                                    Rate = itm.Rate,
                                    Cost = itm.Cost,
                                    Description = itm.Description,
                                    StateID = itm.StateID,
                                    VendorID = itm.VendorID,
                                    Currency = itm.Currency,
                                    Day = itm.Day,
                                    DayQty = itm.DayQty,
                                    BusMFlag = itm.BusMFlag,
                                    ServiceID = itm.ServiceID,
                                };
                                _context.tblQuotaBus.Add(bus);
                            }
                            foreach (var itm in tblQuotaCalcs)
                            {
                                var calcs = new tblQuotaCalc()
                                {
                                    QuotaID = NewQuota.QuotaID,
                                    BusPP = itm.BusPP,
                                    EnGPP = itm.EnGPP,
                                    EntFree = itm.EntFree,
                                    EntPP = itm.EntPP,
                                    GuiPP = itm.GuiPP,
                                    HtlFree = itm.HtlFree,
                                    HtlPP = itm.HtlPP,
                                    LDnFree = itm.LDnFree,
                                    LDnPP = itm.LDnPP,
                                    Manual = itm.Manual,
                                    MscGPP = itm.MscGPP,
                                    MscPP = itm.MscPP,
                                    Pax = itm.Pax,
                                    PercProfit = itm.PercProfit,
                                    PlusFree = itm.PlusFree,
                                    PlusProfit = itm.PlusProfit,
                                    SSpFree = itm.SSpFree,   
                                    LineNo = itm.LineNo,      
                                };
                                _context.tblQuotaCalcs.Add(calcs);
                            }
                            _context.SaveChanges();
                            transaction.Commit();
                        }
                        catch(Exception ex)
                        {
                            Error.errorCode = 121;
                            Error.Message = ex.Message;
                            logger.Error(ex.ToMessageAndCompleteStacktrace());
                            transaction.Rollback();
                        }

                    }
            }

                    if (NewQuota != null)
                        QuotaBData = (from quota in _context.tblQuotations
                                      where quota.QuotaID == NewQuota.QuotaID
                                      select new QuotationDetailsBData()
                                      {
                                          QuotaID = quota.QuotaID,
                                          Accepted = quota.Accepted,
                                          IsGir = quota.IsGir,
                                          SelectedQuotaCountry = quota.QuotaType,
                                          NoOfFlights = quota.NoOfFlights,
                                          SelectedRegion = quota.Region,
                                          QuotaCode = quota.QuotaCode,
                                          SelectedCustomerId = quota.CustomerID,
                                          SelectedSeason = quota.SeasonID,
                                          QuotaName = quota.QuotaName,
                                          QuotaNote = quota.QuotaNote,
                                          SelectedEmployeeId = quota.EmployeeID,
                                          NoNights = quota.NoOfNights,
                                          NoOfMeals = quota.NoOfMeals,
                                          ArrivalDate = quota.ArrivalDate,
                                          SelectedArrivalCity = quota.ArrivalCity,
                                          QuotaDate = quota.QuotaDate,
                                          LastUpDate = quota.LastUpDate,
                                          MaxNoOfPax = quota.MaxNoOfPax,
                                          MinNoOfPax = quota.MinNoOfPax,
                                          IncNoOfPax = quota.IncNoOfPax,
                                          HFreeRoom = quota.HFreeRoom,
                                          HForPaid = quota.HForPaid,
                                          DepartureDate = quota.DepartureDate,
                                          SelectedDepartureCity = quota.DepartureCity,
                                          SelectedFreeCalcType = quota.FreeCalcType,
                                          profit = quota.Profit,
                                          OFreeService = quota.OFreeService,
                                          OForPaid = quota.OForPaid,
                                          Proposal = quota.ProposalWordDoc,
                                      }).SingleOrDefault();

            if (QuotaBData != null)
            {
                // country
                QuotaBData.QuotaCountry = (from ctr in _context.tblCountries
                                           where ctr.Active == true
                                           orderby ctr.OrderNum, ctr.CountryName
                                           select new
                                           {
                                               Key = ctr.CountryID,
                                               Value = ctr.CountryName,
                                           }).ToDictionary(o => o.Key, o => o.Value);
                // region
                QuotaBData.Region = new Dictionary<int, string>();
                var region = _context.tblQuotaRegions.Where(Qreg=>Qreg.Active == true).Select(obj => new { RId = obj.RegionID, region = obj.Region }).OrderBy(obj=>obj.region).ToList();
                foreach (var reg in region)
                    QuotaBData.Region.Add(reg.RId, reg.region);
                // customer
                QuotaBData.CustomerId = (from cust in _context.tblCustomers
                                         where cust.Active == true
                                         orderby cust.OrganizationName, cust.CustomerCode
                                         select new
                                         {
                                             Key = cust.CustomerID,
                                             Value = cust.OrganizationName,
                                         }).ToDictionary(o => o.Key, o => o.Value);
                // Season
                QuotaBData.Season = (from season in _context.tblSeasons
                                     orderby season.IsCurrent descending
                                     select new
                                     {
                                         Key = season.SeasonID,
                                         Value = season.SeasonDescription,
                                     }).ToDictionary(o => o.Key, o => o.Value);
                // Employee
                // var EmpID = _context.Users.Where(usr => usr.UserName == UserName).Select(usr => usr.EmployeeID).SingleOrDefault();
                QuotaBData.Employee = (from user in _context.Users
                                       from emp in _context.tblEmployees
                                       // from SupEmp in _context.tblSupEmployees
                                       //  where user.EmployeeID == emp.EmployeeID && emp.Active == true && (user.EmployeeID == EmpID || emp.SupervisorID == EmpID)
                                       where user.EmployeeID == emp.EmployeeID && emp.Active == true
                                       orderby emp.FirstName, emp.LastName
                                       select new
                                       {
                                           Key = emp.EmployeeID,
                                           Value = emp.FirstName + " " + emp.LastName,
                                       }).ToDictionary(o => o.Key, o => o.Value);
                // arrival city
                QuotaBData.ArrivalCity = new Dictionary<int, string>();
                QuotaBData.DepartureCity = new Dictionary<int, string>();
                var Cities = _context.tblQuotaCities.Where(city => city.Active == true).Select(obj => new { CId = obj.CityID, city = obj.City }).OrderBy(obj => obj.city).ToList();
                foreach (var city in Cities)
                {
                    QuotaBData.ArrivalCity.Add(city.CId, city.city);
                    QuotaBData.DepartureCity.Add(city.CId, city.city);
                }

            }
            QuotaBData.Error = Error;
            return QuotaBData;
        }
        public QuotationDetailsBData NewQuotationBData(string UserName)
        {
            ErrorBInfo Error = new ErrorBInfo();
            QuotationDetailsBData QuotaBData = null;
            tblQuotation NewQuota = null;
            using (var transaction = _context.Database.BeginTransaction(IsolationLevel.Serializable))
            {
                try
                {
                       NewQuota = new tblQuotation()
                        // init default values 
                        {
                            NoOfMeals = 0,
                            NoOfNights = 0,
                            MaxNoOfPax = 45,
                            MinNoOfPax = 20,
                            IncNoOfPax = 5,
                            Profit = 0.85,
                            QuotaCode = "0",
                            LastUpDate = DateTime.Now,
                            QuotaDate = DateTime.Now,
                            HFreeRoom = 1,
                            HForPaid = 20,
                            OFreeService = 1,
                            OForPaid = 25,
                            Active = true,
                        };
                    _context.tblQuotations.Add(NewQuota);
                    _context.SaveChanges();
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    Error.errorCode = 34343;
                    Error.Message = ex.Message;
                    logger.Error(ex.ToMessageAndCompleteStacktrace());
                    transaction.Rollback();
                }
            }
            if (NewQuota != null)
                QuotaBData = (from quota in _context.tblQuotations
                              where quota.QuotaID == NewQuota.QuotaID
                              select new QuotationDetailsBData()
                              {
                                  QuotaID = quota.QuotaID,
                                  Accepted = quota.Accepted,
                                  IsGir = quota.IsGir,
                                  SelectedQuotaCountry = quota.QuotaType,
                                  NoOfFlights = quota.NoOfFlights,
                                  SelectedRegion = quota.Region,
                                  QuotaCode = quota.QuotaCode,
                                  SelectedCustomerId = quota.CustomerID,
                                  SelectedSeason = quota.SeasonID,
                                  QuotaName = quota.QuotaName,
                                  SelectedEmployeeId = quota.EmployeeID,
                                  NoNights = quota.NoOfNights,
                                  NoOfMeals = quota.NoOfMeals,
                                  ArrivalDate = quota.ArrivalDate,
                                  SelectedArrivalCity = quota.ArrivalCity,
                                  QuotaDate = quota.QuotaDate,
                                  LastUpDate = quota.LastUpDate,
                                  MaxNoOfPax = quota.MaxNoOfPax,
                                  MinNoOfPax = quota.MinNoOfPax,
                                  IncNoOfPax = quota.IncNoOfPax,
                                  HFreeRoom = quota.HFreeRoom,
                                  HForPaid = quota.HForPaid,
                                  DepartureDate = quota.DepartureDate,
                                  SelectedDepartureCity = quota.DepartureCity,
                                  SelectedFreeCalcType = quota.FreeCalcType,
                                  profit = quota.Profit,
                                  OFreeService = quota.OFreeService,
                                  OForPaid = quota.OForPaid,
                                  Proposal = quota.ProposalWordDoc,
                              }).SingleOrDefault();
            
            if (QuotaBData != null)
            {
                // country
                QuotaBData.QuotaCountry = (from ctr in _context.tblCountries
                                           where ctr.Active == true
                                           orderby ctr.OrderNum, ctr.CountryName
                                           select new
                                           {
                                               Key = ctr.CountryID,
                                               Value = ctr.CountryName,
                                           }).ToDictionary(o => o.Key, o => o.Value);
                // region
                QuotaBData.Region = new Dictionary<int, string>();
                var region = _context.tblQuotaRegions.Where(Qreg=>Qreg.Active == true).Select(obj => new { RId = obj.RegionID, region = obj.Region }).OrderBy(obj => obj.region).ToList();
                foreach (var reg in region)
                    QuotaBData.Region.Add(reg.RId, reg.region);
                // customer
                QuotaBData.CustomerId = (from cust in _context.tblCustomers
                                         where cust.Active == true
                                         orderby cust.OrganizationName, cust.CustomerCode
                                         select new
                                         {
                                             Key = cust.CustomerID,
                                             Value = cust.OrganizationName,
                                         }).ToDictionary(o => o.Key, o => o.Value);
                // Season
                QuotaBData.Season = (from season in _context.tblSeasons
                                     orderby season.IsCurrent descending
                                     select new
                                     {
                                         Key = season.SeasonID,
                                         Value = season.SeasonDescription,
                                     }).ToDictionary(o => o.Key, o => o.Value);
                // Employee
                // var EmpID = _context.Users.Where(usr => usr.UserName == UserName).Select(usr => usr.EmployeeID).SingleOrDefault();
                QuotaBData.Employee = (from user in _context.Users
                                       from emp in _context.tblEmployees
                                       // from SupEmp in _context.tblSupEmployees
                                       //  where user.EmployeeID == emp.EmployeeID && emp.Active == true && (user.EmployeeID == EmpID || emp.SupervisorID == EmpID)
                                       where user.EmployeeID == emp.EmployeeID && emp.Active == true
                                       orderby emp.FirstName, emp.LastName
                                       select new
                                       {
                                           Key = emp.EmployeeID,
                                           Value = emp.FirstName + " " + emp.LastName,
                                       }).ToDictionary(o => o.Key, o => o.Value);
                // arrival city
                QuotaBData.ArrivalCity = new Dictionary<int, string>();
                QuotaBData.DepartureCity = new Dictionary<int, string>();
                var Cities = _context.tblQuotaCities.Where(city => city.Active == true).Select(obj => new { CId = obj.CityID, city = obj.City }).OrderBy(obj => obj.city).ToList();
                foreach (var city in Cities)
                {
                    QuotaBData.ArrivalCity.Add(city.CId, city.city);
                    QuotaBData.DepartureCity.Add(city.CId, city.city);
                }

            }
            QuotaBData.Error = Error;
            return QuotaBData;
        }
        public QuotaHotelGridBData LoadQuotaHotelGrid(int QuotaId)
        {
            QuotaHotelGridBData QuotaHotelBData = new QuotaHotelGridBData();    
            try
            {
                InitHotelGridBData(QuotaId, QuotaHotelBData);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }

            return QuotaHotelBData;
        }
        public QuotaHotelGridBData NewLineQuotaHotelGrid(int QuotaId)
        {
            QuotaHotelGridBData QuotaHotelBData = new QuotaHotelGridBData();
            QuotaHotelBData.Error = new ErrorBInfo();
            using (var transaction = _context.Database.BeginTransaction(IsolationLevel.Serializable))
            {
                try
                {
                    short LastLine = 0;
                    try
                    {
                        LastLine = (short)_context.tblQuotaHotels.Where(htl => htl.QuotaID == QuotaId).OrderByDescending(obj => obj.LineNo).First().LineNo;
                    }
                    catch(Exception ex)
                    {
                        QuotaHotelBData.Error.errorCode = 454;
                        QuotaHotelBData.Error.Message = ex.Message;
                        logger.Error(ex.ToMessageAndCompleteStacktrace());
                    }
                    short newline = 1;
                    if (LastLine != null && LastLine > 0)
                        newline = Convert.ToInt16(LastLine + 1);
                    var NewHtl = new tblQuotaHotel() { QuotaID = QuotaId, LineNo = newline, Currency = StringHelper.CurrencyUsd, HotelMFlag = true };
                    _context.tblQuotaHotels.Add(NewHtl);
                    _context.SaveChanges();
                    transaction.Commit();   
                }
                catch (Exception ex)
                {
                    QuotaHotelBData.Error.errorCode = 45433;
                    QuotaHotelBData.Error.Message = ex.Message;
                    logger.Error(ex.ToMessageAndCompleteStacktrace());
                    transaction.Rollback();
                }
            }
            InitHotelGridBData(QuotaId, QuotaHotelBData);

            return QuotaHotelBData;
        }
        public QuotaHotelGridBData InsertLineQuotaHotelGrid(int QuotaId, int LineNum)
        {
            QuotaHotelGridBData QuotaHotelBData = new QuotaHotelGridBData();
            QuotaHotelBData.Error = new ErrorBInfo();
            using (var transaction = _context.Database.BeginTransaction(IsolationLevel.Serializable))
            {
                try
                {
                    short LastLine = 0;
                    short FirstLine = 0;
                    try
                    {
                        LastLine = (short)_context.tblQuotaHotels.Where(htl => htl.QuotaID == QuotaId).OrderByDescending(obj => obj.LineNo).First().LineNo;
                        FirstLine = (short)_context.tblQuotaHotels.Where(htl => htl.QuotaID == QuotaId).OrderBy(obj => obj.LineNo).First().LineNo;
                    }
                    catch (Exception ex)
                    {
                        QuotaHotelBData.Error.errorCode = 45433;
                        QuotaHotelBData.Error.Message = ex.Message;
                        logger.Error(ex.ToMessageAndCompleteStacktrace());
                    }
                    if (LineNum >= FirstLine && LineNum <= LastLine)
                    {
                        var NewHtl = new tblQuotaHotel() { QuotaID = QuotaId, LineNo = (short)LineNum, Currency = StringHelper.CurrencyUsd, HotelMFlag = true };
                        var Hotels = _context.tblQuotaHotels.Where(htl => htl.QuotaID == QuotaId && htl.LineNo >= LineNum).OrderBy(obj => obj.LineNo).ToList();
                        foreach (var hotel in Hotels)
                        {
                            hotel.LineNo = Convert.ToInt16(hotel.LineNo + 1);
                        }
                        _context.tblQuotaHotels.Add(NewHtl);
                        _context.SaveChanges();
                        transaction.Commit();
                    }  
                }
                catch (Exception ex)
                {
                    QuotaHotelBData.Error.errorCode = 45433;
                    QuotaHotelBData.Error.Message = ex.Message;
                    logger.Error(ex.ToMessageAndCompleteStacktrace());
                    transaction.Rollback();
                }
            }
            InitHotelGridBData(QuotaId, QuotaHotelBData);
            return QuotaHotelBData;
        }
        public QuotaHotelGridBData UpdateQuotaHotelGrid(List<QuotaHotelGridRowBData> Rows, bool IsNewLine, bool IsInsert, int InsertRow)
        {
            ErrorBInfo Error = new ErrorBInfo();
            int QuotaId = 0;
            if (Rows.Count > 0)
                QuotaId = Rows[0].QuotaID;

            using (var transaction = _context.Database.BeginTransaction(IsolationLevel.Serializable))
            {
                try
                {
                    var hotels = _context.tblQuotaHotels.Where(htl => htl.QuotaID == QuotaId).ToList();
                    foreach (var row in Rows)
                    {
                        var hotel = hotels.Where(htl => htl.LineNo == row.LineNo).SingleOrDefault();
                        if (hotel != null)
                        {
                            hotel.BFreeFlag = row.BFreeFlag;
                            hotel.BGuideFlag = row.BGuideFlag;
                            hotel.Breakfast = row.Breakfast;
                            hotel.BrfType = row.BrfType;
                            hotel.DriverFlag = row.DriverFlag;
                            hotel.FreeFlag = row.FreeFlag;
                            hotel.GuideFlag = row.GuideFlag;
                            hotel.Description = row.HotelDesc;
                            hotel.HotelMFlag = row.HotelMFlag;
                            hotel.Luggage = row.Luggage;
                            hotel.Nights = row.Nights;
                            hotel.OccTax = row.OccTax;
                            hotel.Room = row.Room;
                            hotel.Tax = row.tax;
                            hotel.Currency = row.Currency;
                            hotel.VendorID = row.VendorID;
                        }
                    }
                    _context.SaveChanges();
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    Error.errorCode = 45433;
                    Error.Message = ex.Message;
                    logger.Error(ex.ToMessageAndCompleteStacktrace());
                    transaction.Rollback();
                }
            }
            // Checking special dates
            if (Error.errorCode == 0)
            {
                var VendorIDs = Rows.Where(row => row.VendorID != null && row.VendorID > 0).Select(row => row.VendorID).Distinct().ToList();
                var Vendors = _context.tblVendors.Where(vnd => VendorIDs.Contains(vnd.VendorID)).ToList();
                var CityIDs = Vendors.Where(ven => ven.CityID != null && ven.CityID > 0).Select(ven => ven.CityID).Distinct().ToList();
                var SpecialDates = _context.tblSpecialDates.Where(spdate => VendorIDs.Contains(spdate.VendorID) || CityIDs.Contains(spdate.CityID)).ToList();
                StringBuilder SpecialDateNotes = new StringBuilder();
                tblSpecialDate SpecialDateData = null;
                foreach (var row in Rows)
                {
                    SpecialDateData = null;
                    var StartDate = row.LineDate;
                    var EndDate = row.LineDate.AddDays(Convert.ToInt32(row.Nights) - 1);
                    if (SpecialDates.Any(spdate => spdate.VendorID == row.VendorID && !(spdate.StartDate > EndDate || StartDate > spdate.EndDate) && spdate.Active == true))
                    {
                        SpecialDateData = SpecialDates.Where(spdate => spdate.VendorID == row.VendorID && !(spdate.StartDate > EndDate || StartDate > spdate.EndDate) && spdate.Active == true).FirstOrDefault();
                    }
                    else
                    {
                        if (Vendors.Any(vnd => vnd.VendorID == row.VendorID))
                        {
                            var CityID = Vendors.Where(vnd => vnd.VendorID == row.VendorID).SingleOrDefault().CityID;
                            if (CityID != null && CityID > 0)
                            {
                                if (SpecialDates.Any(spdate => spdate.CityID == CityID && !(spdate.StartDate > EndDate || StartDate > spdate.EndDate) && spdate.Active == true && spdate.IsHotel == true))
                                {
                                    SpecialDateData = SpecialDates.Where(spdate => spdate.CityID == CityID && row.LineDate >= spdate.StartDate && row.LineDate <= spdate.EndDate && spdate.Active == true && spdate.IsHotel == true).FirstOrDefault();
                                }
                            }
                        }
                    }
                    if (SpecialDateData != null)
                    {
                        Error.errorCode = 4445;
                        SpecialDateNotes.Append(Vendors.Where(vnd => vnd.VendorID == row.VendorID).SingleOrDefault().VendorName + " - " + Vendors.Where(vnd => vnd.VendorID == row.VendorID).SingleOrDefault().City + " - " + Convert.ToDateTime(SpecialDateData.StartDate).ToString("dd/MMM/yy") + " - " + Convert.ToDateTime(SpecialDateData.EndDate).ToString("dd/MMM/yy") + " - " + SpecialDateData.Notes + "\n");
                    }
                }
                Error.Message = SpecialDateNotes.ToString();
            }

            QuotaHotelGridBData QuotaHotelBData = new QuotaHotelGridBData();
            if (IsNewLine)
                QuotaHotelBData = NewLineQuotaHotelGrid(QuotaId);
            if (IsInsert)
                QuotaHotelBData = InsertLineQuotaHotelGrid(QuotaId, InsertRow);
            if(IsInsert == false && IsNewLine == false)
                InitHotelGridBData(QuotaId, QuotaHotelBData);
            QuotaHotelBData.Error = Error;
            return QuotaHotelBData;
        }
        public QuotaLunchDinnerGridBData NewLineQuotaLunchDinnerGrid(int QuotaId)
        {
            QuotaLunchDinnerGridBData QuotaLunchBData = new QuotaLunchDinnerGridBData();
            QuotaLunchBData.Error = new ErrorBInfo();
            using (var transaction = _context.Database.BeginTransaction(IsolationLevel.Serializable))
            {
                try
                {
                    short LastLine = 0;
                    try
                    {
                        LastLine = (short)_context.tblQuotaLunDins.Where(lanch => lanch.QuotaID == QuotaId).OrderByDescending(obj => obj.LineNo).First().LineNo;
                    }
                    catch (Exception ex)
                    {
                        QuotaLunchBData.Error.errorCode = 454;
                        QuotaLunchBData.Error.Message = ex.Message;
                        logger.Error(ex.ToMessageAndCompleteStacktrace());
                    }
                    short newline = 1;
                    if (LastLine != null && LastLine > 0)
                        newline = Convert.ToInt16(LastLine + 1);
                    var NewLunch = new tblQuotaLunDin() { QuotaID = QuotaId, LineNo = newline, Currency = StringHelper.CurrencyUsd, DinnerMFlag = true, LunchMFlag = true, Day = Convert.ToByte(newline) };
                    _context.tblQuotaLunDins.Add(NewLunch);
                    _context.SaveChanges();
                    transaction.Commit();     
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    QuotaLunchBData.Error.errorCode = 454;
                    QuotaLunchBData.Error.Message = ex.Message;
                    logger.Error(ex.ToMessageAndCompleteStacktrace());
                }
            }
            InitLunchDinnerGridBData(QuotaId, QuotaLunchBData);
            return QuotaLunchBData;
        }
        public QuotaLunchDinnerGridBData InsertLineQuotaLunchDinnerGrid(int QuotaId, int LineNum)
        {
            QuotaLunchDinnerGridBData QuotaLunchBData = new QuotaLunchDinnerGridBData();
            QuotaLunchBData.Error = new ErrorBInfo();
            using (var transaction = _context.Database.BeginTransaction(IsolationLevel.Serializable))
            {
                try
                {
                    short LastLine = 0;
                    short FirstLine = 0;
                    try
                    {
                        LastLine = (short)_context.tblQuotaLunDins.Where(lunch => lunch.QuotaID == QuotaId).OrderByDescending(obj => obj.LineNo).First().LineNo;
                        FirstLine = (short)_context.tblQuotaLunDins.Where(lunch => lunch.QuotaID == QuotaId).OrderBy(obj => obj.LineNo).First().LineNo;
                    }
                    catch (Exception ex)
                    {
                        QuotaLunchBData.Error.errorCode = 454;
                        QuotaLunchBData.Error.Message = ex.Message;
                        logger.Error(ex.ToMessageAndCompleteStacktrace());
                    }

                    if (LineNum >= FirstLine && LineNum <= LastLine)
                    {
                        var NewLunch = new tblQuotaLunDin() { QuotaID = QuotaId, LineNo = (short)LineNum, Currency = StringHelper.CurrencyUsd, DinnerMFlag = true, LunchMFlag = true, Day = (byte)LineNum };
                        var Lunches = _context.tblQuotaLunDins.Where(lunch => lunch.QuotaID == QuotaId && lunch.LineNo >= LineNum).OrderBy(obj => obj.LineNo).ToList();

                        foreach (var lunch in Lunches)
                        {
                            lunch.LineNo = Convert.ToInt16(lunch.LineNo + 1);
                            lunch.Day = Convert.ToByte(lunch.Day + 1);
                        }
                        _context.tblQuotaLunDins.Add(NewLunch);
                        _context.SaveChanges();
                        transaction.Commit();
                    }
                }

                catch (Exception ex)
                {
                    QuotaLunchBData.Error.errorCode = 454;
                    QuotaLunchBData.Error.Message = ex.Message;
                    logger.Error(ex.ToMessageAndCompleteStacktrace());
                    transaction.Rollback();
                }
            }
            InitLunchDinnerGridBData(QuotaId, QuotaLunchBData);
            return QuotaLunchBData;
        }
        public QuotaLunchDinnerGridBData UpdateQuotaLunchDinnerGrid(List<QuotaLunchDinnerGridRowBData> Rows)
        {
            ErrorBInfo Error = new ErrorBInfo();
            int QuotaId = 0;
            if (Rows.Count > 0)
                QuotaId = Rows[0].QuotaID;

            QuotaLunchDinnerGridBData QuotaLunchBData = new QuotaLunchDinnerGridBData();
            QuotaLunchBData.Error = new ErrorBInfo();
            using (var transaction = _context.Database.BeginTransaction(IsolationLevel.Serializable))
            {
                try
                {
                    var Lunchs = (from lunch in _context.tblQuotaLunDins where lunch.QuotaID == QuotaId select lunch).ToList();
                    foreach (var row in Rows)
                    {
                        var lunch = Lunchs.Where(lch => lch.LineNo == row.LineNo).FirstOrDefault();
                        if (lunch != null)
                        {
                            lunch.VendorID = row.LunchVendorID;
                            lunch.DVendorID = row.DinnerVendorID;
                            lunch.FreeFlag = row.FreeFlag;
                            lunch.GuideFlag = row.GuideFlag;
                            lunch.Description = row.Description;
                            lunch.LunchMFlag = row.LunchMFlag;
                            lunch.DinnerMFlag = row.DinnerMFlag;
                            lunch.Cost = row.Cost;
                            lunch.CostD = row.CostD;
                            lunch.DescriptionD = row.DescriptionD;
                            lunch.FreeFlagD = row.FreeFlagD;
                            lunch.GuideFlagD = row.GuideFlagD;
                            lunch.Day = row.Day;
                            lunch.Currency = row.Currency;
                        }
                    }
                    _context.SaveChanges();
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    Error.errorCode = 454;
                    Error.Message = ex.Message;
                    logger.Error(ex.ToMessageAndCompleteStacktrace());
                    transaction.Rollback();
                }
            }
            // Check Special Dates
            if (Error.errorCode == 0)
            {
                var VendorIDs = Rows.Where(row => row.LunchVendorID != null && row.LunchVendorID > 0).Select(row => row.LunchVendorID).Distinct().ToList();
                VendorIDs.AddRange(Rows.Where(row => row.DinnerVendorID != null && row.DinnerVendorID > 0).Select(row => row.DinnerVendorID).Distinct().ToList());
                var Vendors = _context.tblVendors.Where(vnd => VendorIDs.Contains(vnd.VendorID)).ToList();
                var CityIDs = Vendors.Where(ven => ven.CityID != null && ven.CityID > 0).Select(ven => ven.CityID).Distinct().ToList();
                var SpecialDates = _context.tblSpecialDates.Where(spdate => VendorIDs.Contains(spdate.VendorID) || CityIDs.Contains(spdate.CityID)).ToList();
                StringBuilder SpecialDateNotes = new StringBuilder();
                tblSpecialDate SpecialDateData = null;
                foreach (var row in Rows)
                {
                    var StartDate = row.LineDate;
                    SpecialDateData = null;
                    if (SpecialDates.Any(spdate => spdate.VendorID == row.LunchVendorID && spdate.StartDate <= StartDate && StartDate <= spdate.EndDate && spdate.Active == true))
                    {
                        SpecialDateData = SpecialDates.Where(spdate => spdate.VendorID == row.LunchVendorID && spdate.StartDate <= StartDate && StartDate <= spdate.EndDate && spdate.Active == true).FirstOrDefault();
                    }
                    else
                    {
                        if (Vendors.Any(vnd => vnd.VendorID == row.LunchVendorID))
                        {
                            var CityID = Vendors.Where(vnd => vnd.VendorID == row.LunchVendorID).SingleOrDefault().CityID;
                            if (CityID != null && CityID > 0)
                            {
                                if (SpecialDates.Any(spdate => spdate.CityID == CityID && spdate.StartDate <= StartDate && StartDate <= spdate.EndDate && spdate.Active == true && spdate.IsRestaurant == true))
                                {
                                    SpecialDateData = SpecialDates.Where(spdate => spdate.CityID == CityID && row.LineDate >= spdate.StartDate && row.LineDate <= spdate.EndDate && spdate.Active == true && spdate.IsRestaurant == true).FirstOrDefault();
                                }
                            }
                        }
                    }
                    if (SpecialDateData != null)
                    {
                        Error.errorCode = 4445;
                        SpecialDateNotes.Append(Vendors.Where(vnd => vnd.VendorID == row.LunchVendorID).SingleOrDefault().VendorName + " - " + Vendors.Where(vnd => vnd.VendorID == row.LunchVendorID).SingleOrDefault().City + " - " + Convert.ToDateTime(SpecialDateData.StartDate).ToString("dd/MMM/yy") + " - " + Convert.ToDateTime(SpecialDateData.EndDate).ToString("dd/MMM/yy") + " - " + SpecialDateData.Notes + "\n");
                    }
                    SpecialDateData = null;
                    if (SpecialDates.Any(spdate => spdate.VendorID == row.DinnerVendorID && spdate.StartDate <= StartDate && StartDate <= spdate.EndDate && spdate.Active == true))
                    {
                        SpecialDateData = SpecialDates.Where(spdate => spdate.VendorID == row.DinnerVendorID && spdate.StartDate <= StartDate && StartDate <= spdate.EndDate && spdate.Active == true).FirstOrDefault();
                    }
                    else
                    {
                        if (Vendors.Any(vnd => vnd.VendorID == row.DinnerVendorID))
                        {
                            var CityID = Vendors.Where(vnd => vnd.VendorID == row.DinnerVendorID).SingleOrDefault().CityID;
                            if (CityID != null && CityID > 0)
                            {
                                if (SpecialDates.Any(spdate => spdate.CityID == CityID && spdate.StartDate <= StartDate && StartDate <= spdate.EndDate && spdate.Active == true && spdate.IsRestaurant == true))
                                {
                                    SpecialDateData = SpecialDates.Where(spdate => spdate.CityID == CityID && row.LineDate >= spdate.StartDate && row.LineDate <= spdate.EndDate && spdate.Active == true && spdate.IsRestaurant == true).FirstOrDefault();
                                }
                            }
                        }
                    }
                    if (SpecialDateData != null)
                    {
                        Error.errorCode = 4445;
                        SpecialDateNotes.Append(Vendors.Where(vnd => vnd.VendorID == row.DinnerVendorID).SingleOrDefault().VendorName + " - " + Vendors.Where(vnd => vnd.VendorID == row.DinnerVendorID).SingleOrDefault().City + " - " + Convert.ToDateTime(SpecialDateData.StartDate).ToString("dd/MMM/yy") + " - " + Convert.ToDateTime(SpecialDateData.EndDate).ToString("dd/MMM/yy") + " - " + SpecialDateData.Notes + "\n");
                    }
                }
                Error.Message = SpecialDateNotes.ToString();
            }
            InitLunchDinnerGridBData(QuotaId, QuotaLunchBData);
            QuotaLunchBData.Error = Error;
            return QuotaLunchBData;
        }
        public QuotaEntranceGridBData NewLineQuotaEntranceGrid(int QuotaId)
        {
            QuotaEntranceGridBData QuotaEntranceBData = new QuotaEntranceGridBData();
            QuotaEntranceBData.Error = new ErrorBInfo();
            using (var transaction = _context.Database.BeginTransaction(IsolationLevel.Serializable))
            {
                try
                {
                    short LastLine = 0;
                    try
                    {
                        LastLine = (short)_context.tblQuotaEntrances.Where(ent => ent.QuotaID == QuotaId).OrderByDescending(obj => obj.LineNo).First().LineNo;
                    }
                    catch(Exception ex)
                    {
                        QuotaEntranceBData.Error.errorCode = 454;
                        QuotaEntranceBData.Error.Message = ex.Message;
                        logger.Error(ex.ToMessageAndCompleteStacktrace());
                    }
                    short newline = 1;
                    if (LastLine != null && LastLine > 0)
                        newline = Convert.ToInt16(LastLine + 1);
                    var NewEntr = new tblQuotaEntrance() { QuotaID = QuotaId, LineNo = newline, Currency = StringHelper.CurrencyUsd, EntranceMFlag = true, Day = (byte)newline };
                    _context.tblQuotaEntrances.Add(NewEntr);
                    _context.SaveChanges();
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    QuotaEntranceBData.Error.errorCode = 454;
                    QuotaEntranceBData.Error.Message = ex.Message;
                    logger.Error(ex.ToMessageAndCompleteStacktrace());
                    transaction.Rollback();
                }
            }
            InitEntranceGridBData(QuotaId, QuotaEntranceBData);
            return QuotaEntranceBData;
        }
        public QuotaEntranceGridBData InsertLineQuotaEntranceGrid(int QuotaId, int LineNum)
        {
            QuotaEntranceGridBData QuotaEntranceBData = new QuotaEntranceGridBData();
            QuotaEntranceBData.Error = new ErrorBInfo();
            using (var transaction = _context.Database.BeginTransaction(IsolationLevel.Serializable))
            {
                try
                {
                    short LastLine = 0;
                    short FirstLine = 0;
                    try
                    {
                        LastLine = (short)_context.tblQuotaEntrances.Where(ent => ent.QuotaID == QuotaId).OrderByDescending(obj => obj.LineNo).First().LineNo;
                        FirstLine = (short)_context.tblQuotaEntrances.Where(ent => ent.QuotaID == QuotaId).OrderBy(obj => obj.LineNo).First().LineNo;
                    }
                    catch (Exception ex)
                    {
                        QuotaEntranceBData.Error.errorCode = 454;
                        QuotaEntranceBData.Error.Message = ex.Message;
                        logger.Error(ex.ToMessageAndCompleteStacktrace());
                    }

                    if (LineNum >= FirstLine && LineNum <= LastLine)
                    {
                        var NewEntr = new tblQuotaEntrance() { QuotaID = QuotaId, LineNo = (short)LineNum, Currency = StringHelper.CurrencyUsd, EntranceMFlag = true, Day = (byte)LineNum };
                        var Entrances = _context.tblQuotaEntrances.Where(ent => ent.QuotaID == QuotaId && ent.LineNo >= LineNum).OrderBy(obj => obj.LineNo).ToList();

                        foreach (var ent in Entrances)
                        {
                            ent.LineNo = Convert.ToInt16(ent.LineNo + 1);
                        }
                        _context.tblQuotaEntrances.Add(NewEntr);
                        _context.SaveChanges();
                        transaction.Commit();
                    }  
                }
                catch (Exception ex)
                {
                    QuotaEntranceBData.Error.errorCode = 454;
                    QuotaEntranceBData.Error.Message = ex.Message;
                    logger.Error(ex.ToMessageAndCompleteStacktrace());
                    transaction.Rollback();
                }
            }
            InitEntranceGridBData(QuotaId, QuotaEntranceBData);
            return QuotaEntranceBData;
        }
        public QuotaEntranceGridBData UpdateQuotaEntranceGrid(List<QuotaEntranceGridRowBData> Rows)
        {
            ErrorBInfo Error = new ErrorBInfo();
            int QuotaId = 0;
            if (Rows.Count > 0)
                QuotaId = Rows[0].QuotaID;

            QuotaEntranceGridBData QuotaEntranceBData = new QuotaEntranceGridBData();
            QuotaEntranceBData.Error = new ErrorBInfo();
            using (var transaction = _context.Database.BeginTransaction(IsolationLevel.Serializable))
            {
                try
                {
                    var Entrances = (from ent in _context.tblQuotaEntrances where ent.QuotaID == QuotaId select ent).ToList();
                    foreach (var row in Rows)
                    {
                        var entr = Entrances.Where(ent => ent.LineNo == row.LineNo).FirstOrDefault();
                        if (entr != null)
                        {
                            entr.VendorID = row.VendorID;
                            entr.FreeFlag = row.FreeFlag;
                            entr.GuideFlag = row.GuideFlag;
                            entr.Description = row.Description;
                            entr.Cost = row.Cost;
                            entr.CostG = row.CostG;
                            entr.Day = row.Day;
                            entr.Currency = row.Currency;
                            entr.EntranceMFlag = row.EntranceMFlag;
                        }
                    }

                    _context.SaveChanges();
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    Error.errorCode = 454;
                    Error.Message = ex.Message;
                    logger.Error(ex.ToMessageAndCompleteStacktrace());
                    transaction.Rollback();
                }
            }
            // Checking Special Dates
            if(Error.errorCode == 0)
            {
                var VendorIDs = Rows.Where(row => row.VendorID != null && row.VendorID > 0).Select(row => row.VendorID).Distinct().ToList();
                var Vendors = _context.tblVendors.Where(vnd => VendorIDs.Contains(vnd.VendorID)).ToList();
                var CityIDs = Vendors.Where(ven => ven.CityID != null && ven.CityID > 0).Select(ven => ven.CityID).Distinct().ToList();
                var SpecialDates = _context.tblSpecialDates.Where(spdate => VendorIDs.Contains(spdate.VendorID) || CityIDs.Contains(spdate.CityID)).ToList();
                StringBuilder SpecialDateNotes = new StringBuilder();
                tblSpecialDate SpecialDateData = null;
                foreach (var row in Rows)
                {
                    SpecialDateData = null;
                    var StartDate = row.LineDate;
                    if (SpecialDates.Any(spdate => spdate.VendorID == row.VendorID && spdate.StartDate <= StartDate && StartDate <= spdate.EndDate && spdate.Active == true))
                    {
                        SpecialDateData = SpecialDates.Where(spdate => spdate.VendorID == row.VendorID && spdate.StartDate <= StartDate && StartDate <= spdate.EndDate && spdate.Active == true).FirstOrDefault();
                    }
                    else
                    {
                        if (Vendors.Any(vnd => vnd.VendorID == row.VendorID))
                        {
                            var CityID = Vendors.Where(vnd => vnd.VendorID == row.VendorID).SingleOrDefault().CityID;
                            if (CityID != null && CityID > 0)
                            {
                                if (SpecialDates.Any(spdate => spdate.CityID == CityID && spdate.StartDate <= StartDate && StartDate <= spdate.EndDate && spdate.Active == true && spdate.IsAttraction == true))
                                {
                                    SpecialDateData = SpecialDates.Where(spdate => spdate.CityID == CityID && row.LineDate >= spdate.StartDate && row.LineDate <= spdate.EndDate && spdate.Active == true && spdate.IsAttraction == true).FirstOrDefault();
                                }
                            }
                        }
                    }
                    if (SpecialDateData != null)
                    {
                        Error.errorCode = 4445;
                        SpecialDateNotes.Append(Vendors.Where(vnd => vnd.VendorID == row.VendorID).SingleOrDefault().VendorName + " - " + Vendors.Where(vnd => vnd.VendorID == row.VendorID).SingleOrDefault().City + " - " + Convert.ToDateTime(SpecialDateData.StartDate).ToString("dd/MMM/yy") + " - " + Convert.ToDateTime(SpecialDateData.EndDate).ToString("dd/MMM/yy") + " - " + SpecialDateData.Notes + "\n");
                    }
                }
                Error.Message = SpecialDateNotes.ToString();
            }
            InitEntranceGridBData(QuotaId, QuotaEntranceBData);
            QuotaEntranceBData.Error = Error;
            return QuotaEntranceBData;
        }
        public QuotaMiscGridBData NewLineQuotaMiscGrid(int QuotaId)
        {
            QuotaMiscGridBData QuotaMiscBData = new QuotaMiscGridBData();
            QuotaMiscBData.Error = new ErrorBInfo();
            using (var transaction = _context.Database.BeginTransaction(IsolationLevel.Serializable))
            {
                try
                {
                    short LastLine = 0;
                    try
                    {
                        LastLine = (short)_context.tblQuotaEntrGrps.Where(misc => misc.QuotaID == QuotaId).OrderByDescending(obj => obj.LineNo).First().LineNo;
                    }
                    catch(Exception ex)
                    {
                        QuotaMiscBData.Error.errorCode = 454;
                        QuotaMiscBData.Error.Message = ex.Message;
                        logger.Error(ex.ToMessageAndCompleteStacktrace());
                    }
                    short newline = 1;
                    if (LastLine != null && LastLine > 0)
                        newline = Convert.ToInt16(LastLine + 1);
                    var NewMisc = new tblQuotaEntrGrp() { QuotaID = QuotaId, LineNo = newline, Currency = StringHelper.CurrencyUsd, Day = (byte)newline };
                    _context.tblQuotaEntrGrps.Add(NewMisc);
                    _context.SaveChanges();
                    transaction.Commit();
                    
                }
                catch (Exception ex)
                {
                    QuotaMiscBData.Error.errorCode = 454;
                    QuotaMiscBData.Error.Message = ex.Message;
                    logger.Error(ex.ToMessageAndCompleteStacktrace());
                    transaction.Rollback();
                }
            }
            InitMiscGridBData(QuotaId, QuotaMiscBData);
            return QuotaMiscBData;
        }
        public QuotaMiscGridBData InsertLineQuotaMiscGrid(int QuotaId, int LineNum)
        {
            QuotaMiscGridBData QuotaMiscBData = new QuotaMiscGridBData();
            QuotaMiscBData.Error = new ErrorBInfo();
            using (var transaction = _context.Database.BeginTransaction(IsolationLevel.Serializable))
            {
                try
                {
                    short LastLine = 0;
                    short FirstLine = 0;
                    try
                    {
                        LastLine = (short)_context.tblQuotaEntrGrps.Where(misc => misc.QuotaID == QuotaId).OrderByDescending(obj => obj.LineNo).First().LineNo;
                        FirstLine = (short)_context.tblQuotaEntrGrps.Where(misc => misc.QuotaID == QuotaId).OrderBy(obj => obj.LineNo).First().LineNo;
                    }
                    catch(Exception ex)
                    {
                        QuotaMiscBData.Error.errorCode = 454;
                        QuotaMiscBData.Error.Message = ex.Message;
                        logger.Error(ex.ToMessageAndCompleteStacktrace());
                    }

                    if (LineNum >= FirstLine && LineNum <= LastLine)
                    {
                        var NewMisc = new tblQuotaEntrGrp() { QuotaID = QuotaId, LineNo = (short)LineNum, Currency = StringHelper.CurrencyUsd, Day = (byte)LineNum };
                        var Miscs = _context.tblQuotaEntrGrps.Where(misc => misc.QuotaID == QuotaId && misc.LineNo >= LineNum).OrderBy(obj => obj.LineNo).ToList();
                        foreach (var misc in Miscs)
                        {
                            misc.LineNo = Convert.ToInt16(misc.LineNo + 1);
                        }
                        _context.tblQuotaEntrGrps.Add(NewMisc);
                        _context.SaveChanges();
                        transaction.Commit();
                    }      
                }
                catch (Exception ex)
                {
                    QuotaMiscBData.Error.errorCode = 454;
                    QuotaMiscBData.Error.Message = ex.Message;
                    logger.Error(ex.ToMessageAndCompleteStacktrace());
                    transaction.Rollback();
                }
            }
            InitMiscGridBData(QuotaId, QuotaMiscBData);
            return QuotaMiscBData;
        }
        public QuotaMiscGridBData UpdateQuotaMiscGrid(List<QuotaMiscGridRowBData> Rows)
        {
            int QuotaId = 0;
            if (Rows.Count > 0)
                QuotaId = Rows[0].QuotaID;

            QuotaMiscGridBData QuotaMiscBData = new QuotaMiscGridBData();
            QuotaMiscBData.Error = new ErrorBInfo();
            using (var transaction = _context.Database.BeginTransaction(IsolationLevel.Serializable))
            {
                try
                {
                    var Miscs = (from misc in _context.tblQuotaEntrGrps where misc.QuotaID == QuotaId select misc).ToList();
                    foreach (var row in Rows)
                    {
                        var msc = Miscs.Where(misc => misc.LineNo == row.LineNo).FirstOrDefault();
                        if (msc != null)
                        {
                            msc.Description = row.Description;
                            msc.Cost = row.Cost;
                            msc.CostG = row.CostG;
                            msc.Day = row.Day;
                            msc.Currency = row.Currency;
                            msc.PlaneFlag = row.PlaneFlag;
                        }
                    }

                    _context.SaveChanges();
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    QuotaMiscBData.Error.errorCode = 454;
                    QuotaMiscBData.Error.Message = ex.Message;
                    logger.Error(ex.ToMessageAndCompleteStacktrace());
                    transaction.Rollback();
                }
            }
            InitMiscGridBData(QuotaId, QuotaMiscBData);
            return QuotaMiscBData;
        }
        public QuotaBusGridBData NewLineQuotaBusGrid(int QuotaId)
        {
            QuotaBusGridBData QuotaBusBData = new QuotaBusGridBData();
            QuotaBusBData.Error = new ErrorBInfo();
            using (var transaction = _context.Database.BeginTransaction(IsolationLevel.Serializable))
            {
                try
                {
                    short LastLine = 0;
                    try
                    {
                        LastLine = (short)_context.tblQuotaBus.Where(bus => bus.QuotaID == QuotaId).OrderByDescending(obj => obj.LineNo).First().LineNo;
                    }
                    catch(Exception ex)
                    {
                        QuotaBusBData.Error.errorCode = 454;
                        QuotaBusBData.Error.Message = ex.Message;
                        logger.Error(ex.ToMessageAndCompleteStacktrace());
                    }
                    short newline = 1;
                    if (LastLine != null && LastLine > 0)
                        newline = Convert.ToInt16(LastLine + 1);
                    var NewBus = new tblQuotaBu() { QuotaID = QuotaId, LineNo = newline, DayQty = 1, Qty = 1, Day = (byte)newline, Currency = StringHelper.CurrencyUsd, BusMFlag = true };
                    _context.tblQuotaBus.Add(NewBus);
                    _context.SaveChanges();
                    transaction.Commit();
                    
                }
                catch (Exception ex)
                {
                    QuotaBusBData.Error.errorCode = 454;
                    QuotaBusBData.Error.Message = ex.Message;
                    logger.Error(ex.ToMessageAndCompleteStacktrace());
                    transaction.Rollback();
                }
            }
            InitBusGridBData(QuotaId, QuotaBusBData);
            return QuotaBusBData;
        }
        public QuotaBusGridBData InsertLineQuotaBusGrid(int QuotaId, int LineNum)
        {
            QuotaBusGridBData QuotaBusBData = new QuotaBusGridBData();
            QuotaBusBData.Error = new ErrorBInfo();
            using (var transaction = _context.Database.BeginTransaction(IsolationLevel.Serializable))
            {
                try
                {
                    short LastLine = 0;
                    short FirstLine = 0;
                    try
                    {
                        LastLine = (short)_context.tblQuotaBus.Where(bus => bus.QuotaID == QuotaId).OrderByDescending(obj => obj.LineNo).First().LineNo;
                        FirstLine = (short)_context.tblQuotaBus.Where(bus => bus.QuotaID == QuotaId).OrderBy(obj => obj.LineNo).First().LineNo;
                    }
                    catch(Exception ex)
                    {
                        QuotaBusBData.Error.errorCode = 454;
                        QuotaBusBData.Error.Message = ex.Message;
                        logger.Error(ex.ToMessageAndCompleteStacktrace());
                    }

                    if (LineNum >= FirstLine && LineNum <= LastLine)
                    {
                        var NewBus = new tblQuotaBu() { QuotaID = QuotaId, LineNo = (short)LineNum, DayQty = 1, Qty = 1, Day = (byte)LineNum, Currency = StringHelper.CurrencyUsd, BusMFlag = true };
                        var Busses = _context.tblQuotaBus.Where(bus => bus.QuotaID == QuotaId && bus.LineNo >= LineNum).OrderBy(obj => obj.LineNo).ToList();
                        
                        foreach (var bus in Busses)
                        {
                            bus.LineNo = Convert.ToInt16(bus.LineNo + 1);
                        }

                        _context.tblQuotaBus.Add(NewBus);
                        _context.SaveChanges();
                        transaction.Commit();
                    }  
                }
                catch (Exception ex)
                {
                    QuotaBusBData.Error.errorCode = 454;
                    QuotaBusBData.Error.Message = ex.Message;
                    logger.Error(ex.ToMessageAndCompleteStacktrace());
                    transaction.Rollback();
                }
            }
            InitBusGridBData(QuotaId, QuotaBusBData);
            return QuotaBusBData;
        }
        public QuotaBusGridBData UpdateQuotaBusGrid(List<QuotaBusGridRowBData> Rows)
        {
            ErrorBInfo Error = new ErrorBInfo();
            int QuotaId = 0;
            if (Rows.Count > 0)
                QuotaId = Rows[0].QuotaID;

            QuotaBusGridBData QuotaBusBData = new QuotaBusGridBData();
            QuotaBusBData.Error = new ErrorBInfo();
            using (var transaction = _context.Database.BeginTransaction(IsolationLevel.Serializable))
            {
                try
                {
                    var Busses = (from bus in _context.tblQuotaBus where bus.QuotaID == QuotaId select bus).ToList();
                    foreach (var row in Rows)
                    {
                        var Bus = Busses.Where(bus => bus.LineNo == row.LineNo).FirstOrDefault();
                        if (Bus != null)
                        {
                            Bus.Description = row.Description;
                            if (row.DayQty > 1)
                                Bus.Cost = row.Rate * row.Qty * row.DayQty;
                            else
                                Bus.Cost = row.Rate * row.Qty;
                            Bus.Qty = row.Qty;
                            Bus.Rate = row.Rate;
                            Bus.Day = row.Day;
                            Bus.DayQty = row.DayQty;
                            Bus.Currency = row.Currency;
                            Bus.VendorID = row.VendorID;
                            Bus.BusMFlag = row.BusMFlag;
                            Bus.ServiceID = row.ServiceID;
                        }
                    }

                    _context.SaveChanges();
                    transaction.Commit();  
                }
                catch (Exception ex)
                {
                    Error.errorCode = 454;
                    Error.Message = ex.Message;
                    logger.Error(ex.ToMessageAndCompleteStacktrace());
                    transaction.Rollback();
                }
            }
            //Checking Special Dates
            if(Error.errorCode == 0)
            {
                var VendorIDs = Rows.Where(row => row.VendorID != null && row.VendorID > 0).Select(row => row.VendorID).Distinct().ToList();
                var Vendors = _context.tblVendors.Where(vnd => VendorIDs.Contains(vnd.VendorID)).ToList();
                var CityIDs = Vendors.Where(ven => ven.CityID != null && ven.CityID > 0).Select(ven => ven.CityID).Distinct().ToList();
                var SpecialDates = _context.tblSpecialDates.Where(spdate => VendorIDs.Contains(spdate.VendorID) || CityIDs.Contains(spdate.CityID)).ToList();
                StringBuilder SpecialDateNotes = new StringBuilder();
                tblSpecialDate SpecialDateData = null;
                foreach (var row in Rows)
                {
                    SpecialDateData = null;
                    var StartDate = row.LineDate;
                    if (SpecialDates.Any(spdate => spdate.VendorID == row.VendorID && spdate.StartDate <= StartDate && StartDate <= spdate.EndDate && spdate.Active == true))
                    {
                        SpecialDateData = SpecialDates.Where(spdate => spdate.VendorID == row.VendorID && spdate.StartDate <= StartDate && StartDate <= spdate.EndDate && spdate.Active == true).FirstOrDefault();
                    }
                    else
                    {
                        if (Vendors.Any(vnd => vnd.VendorID == row.VendorID))
                        {
                            var CityID = Vendors.Where(vnd => vnd.VendorID == row.VendorID).SingleOrDefault().CityID;
                            if (CityID != null && CityID > 0)
                            {
                                if (SpecialDates.Any(spdate => spdate.CityID == CityID && spdate.StartDate <= StartDate && StartDate <= spdate.EndDate && spdate.Active == true && spdate.IsBus == true))
                                {
                                    SpecialDateData = SpecialDates.Where(spdate => spdate.CityID == CityID && row.LineDate >= spdate.StartDate && row.LineDate <= spdate.EndDate && spdate.Active == true && spdate.IsBus == true).FirstOrDefault();
                                }
                            }
                        }
                    }
                    if (SpecialDateData != null)
                    {
                        Error.errorCode = 4445;
                        SpecialDateNotes.Append(Vendors.Where(vnd => vnd.VendorID == row.VendorID).SingleOrDefault().VendorName + " - " + Vendors.Where(vnd => vnd.VendorID == row.VendorID).SingleOrDefault().City + " - " + Convert.ToDateTime(SpecialDateData.StartDate).ToString("dd/MMM/yy") + " - " + Convert.ToDateTime(SpecialDateData.EndDate).ToString("dd/MMM/yy") + " - " + SpecialDateData.Notes + "\n");
                    }
                }
                Error.Message = SpecialDateNotes.ToString();
            }
            InitBusGridBData(QuotaId, QuotaBusBData);
            QuotaBusBData.Error = Error;
            return QuotaBusBData;
        }
        public QuotaGuideGridBData NewLineQuotaGuideGrid(int QuotaId)
        {
            QuotaGuideGridBData QuotaGuideBData = new QuotaGuideGridBData();
            QuotaGuideBData.Error = new ErrorBInfo();
            using (var transaction = _context.Database.BeginTransaction(IsolationLevel.Serializable))
            {
                try
                {
                    short LastLine = 0;
                    try
                    {
                        LastLine = (short)_context.tblQuotaGuides.Where(guide => guide.QuotaID == QuotaId).OrderByDescending(obj => obj.LineNo).First().LineNo;
                    }
                    catch(Exception ex)
                    {
                        QuotaGuideBData.Error.errorCode = 454;
                        QuotaGuideBData.Error.Message = ex.Message;
                        logger.Error(ex.ToMessageAndCompleteStacktrace());
                    }
                    short newline = 1;
                    if (LastLine > 0)
                        newline = Convert.ToInt16(LastLine + 1);
                    var NewGuide = new tblQuotaGuide() { QuotaID = QuotaId, LineNo = newline, DayQty = 1, Qty = 1, Day = (byte)newline, Currency = StringHelper.CurrencyUsd, GuideMFlag = true };
                    _context.tblQuotaGuides.Add(NewGuide);
                    _context.SaveChanges();
                    transaction.Commit();
                    
                }
                catch (Exception ex)
                {
                    QuotaGuideBData.Error.errorCode = 454;
                    QuotaGuideBData.Error.Message = ex.Message;
                    logger.Error(ex.ToMessageAndCompleteStacktrace());
                    transaction.Rollback();
                }
            }
            InitGuideGridBData(QuotaId, QuotaGuideBData);
            return QuotaGuideBData;
        }
        public QuotaGuideGridBData InsertLineQuotaGuideGrid(int QuotaId, int LineNum)
        {
            QuotaGuideGridBData QuotaGuideBData = new QuotaGuideGridBData();
            QuotaGuideBData.Error = new ErrorBInfo();
            using (var transaction = _context.Database.BeginTransaction(IsolationLevel.Serializable))
            {
                try
                {
                    short LastLine = 0;
                    short FirstLine = 0;
                    try
                    {
                        LastLine = (short)_context.tblQuotaGuides.Where(guide => guide.QuotaID == QuotaId).OrderByDescending(obj => obj.LineNo).First().LineNo;
                        FirstLine = (short)_context.tblQuotaGuides.Where(guide => guide.QuotaID == QuotaId).OrderBy(obj => obj.LineNo).First().LineNo;
                    }
                    catch(Exception ex)
                    {
                        QuotaGuideBData.Error.errorCode = 454;
                        QuotaGuideBData.Error.Message = ex.Message;
                        logger.Error(ex.ToMessageAndCompleteStacktrace());
                    }

                    if (LineNum >= FirstLine && LineNum <= LastLine)
                    {
                        var NewGuide = new tblQuotaGuide() { QuotaID = QuotaId, LineNo = (short)LineNum, DayQty = 1, Qty = 1, Day = (byte)LineNum, Currency = StringHelper.CurrencyUsd, GuideMFlag = true };
                        var Guides = _context.tblQuotaGuides.Where(guide => guide.QuotaID == QuotaId && guide.LineNo >= LineNum).OrderBy(obj => obj.LineNo).ToList();
                        foreach (var guide in Guides)
                        {
                            guide.LineNo = Convert.ToInt16(guide.LineNo + 1);
                        }

                        _context.tblQuotaGuides.Add(NewGuide);
                        _context.SaveChanges();
                        transaction.Commit();
                    }     
                }
                catch (Exception ex)
                {
                    QuotaGuideBData.Error.errorCode = 454;
                    QuotaGuideBData.Error.Message = ex.Message;
                    logger.Error(ex.ToMessageAndCompleteStacktrace());
                    transaction.Rollback();
                }
            }
            InitGuideGridBData(QuotaId, QuotaGuideBData);
            return QuotaGuideBData;
        }
        public QuotaGuideGridBData UpdateQuotaGuideGrid(List<QuotaGuideGridRowBData> Rows)
        {
            int QuotaId = 0;
            if (Rows.Count > 0)
                QuotaId = Rows[0].QuotaID;

            QuotaGuideGridBData QuotaGuideBData = new QuotaGuideGridBData();
            QuotaGuideBData.Error = new ErrorBInfo();
            using (var transaction = _context.Database.BeginTransaction(IsolationLevel.Serializable))
            {
                try
                {
                    var Guides = (from guide in _context.tblQuotaGuides where guide.QuotaID == QuotaId select guide).ToList();
                    foreach (var row in Rows)
                    {
                        var guide = Guides.Where(guid => guid.LineNo == row.LineNo).FirstOrDefault();
                        if (guide != null)
                        {
                            guide.GuideID = row.GuideID;
                            guide.Description = row.Description;
                            if (row.DayQty > 1)
                                guide.Cost = row.Rate * row.Qty * row.DayQty;
                            else
                                guide.Cost = row.Rate * row.Qty;
                            guide.Qty = row.Qty;
                            guide.Rate = row.Rate;
                            guide.Day = row.Day;
                            guide.DayQty = row.DayQty;
                            guide.Currency = row.Currency;
                            guide.GuideMFlag = row.GuideMFlag;
                            guide.ServiceID = row.ServiceID;
                        }
                    }
                    _context.SaveChanges();
                    transaction.Commit();    
                }
                catch (Exception ex)
                {
                    QuotaGuideBData.Error.errorCode = 454;
                    QuotaGuideBData.Error.Message = ex.Message;
                    logger.Error(ex.ToMessageAndCompleteStacktrace());
                    transaction.Rollback();
                }
            }
            InitGuideGridBData(QuotaId, QuotaGuideBData);
            return QuotaGuideBData;
        }
        public QuotaLunchDinnerGridBData LoadQuotaLunchDinnerGrid(int QuotaId)
        {
            QuotaLunchDinnerGridBData QuotaLunchBData = new QuotaLunchDinnerGridBData();   
            try
            {
                InitLunchDinnerGridBData(QuotaId, QuotaLunchBData);
            }    
            catch (Exception ex)
            {
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return QuotaLunchBData;
        }
        public QuotaEntranceGridBData LoadQuotaEntranceGrid(int QuotaId)
        {
            QuotaEntranceGridBData QuotaEntranceBData = new QuotaEntranceGridBData();
            try
            {
                InitEntranceGridBData(QuotaId, QuotaEntranceBData);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return QuotaEntranceBData;
        }
        public QuotaBusGridBData LoadQuotaBusGrid(int QuotaId)
        {
            QuotaBusGridBData QuotaBusBData = new QuotaBusGridBData();   
            try
            {
                InitBusGridBData(QuotaId, QuotaBusBData);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return QuotaBusBData;
        }
        public QuotaGuideGridBData LoadQuotaGuideGrid(int QuotaId)
        {
            QuotaGuideGridBData QuotaGuideBData = new QuotaGuideGridBData();    
            try
            {
                InitGuideGridBData(QuotaId, QuotaGuideBData);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return QuotaGuideBData;
        }
        public QuotaMiscGridBData LoadQuotaMiscGrid(int QuotaId)
        {
            QuotaMiscGridBData QuotaMiscBData = new QuotaMiscGridBData();
            try
            {
                InitMiscGridBData(QuotaId, QuotaMiscBData);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return QuotaMiscBData;
        }
        public QuotaCostPriceGridBData LoadQuotaCostPriceGrid(int QuotaId, string FreeCalcType)
        {
            QuotaCostPriceGridBData QuotaCPriceBData = new QuotaCostPriceGridBData();
            InitCostPriceGridBData(QuotaId, QuotaCPriceBData, FreeCalcType);

            return QuotaCPriceBData;
        }
        public QuotaCostPriceGridBData CalcQuotaCostPriceGrid(int QuotaId, QuotaCostPriceGridBData Data, string FreeCalcType)
        {
            QuotaCostPriceGridBData QuotaCostPriceBData = new QuotaCostPriceGridBData();
            QuotaCostPriceBData.Error = new ErrorBInfo();     
            
            int LinCount;
            int PaxCount;
            int PaxMin;
            int PaxMax;
            int HFreeR;
            int OFreeS;
            int HForP;
            int OForP;
            int PaxInc;
            decimal CostHtl=0;
            decimal CostHtlCad = 0;
            decimal CostLDn=0;
            decimal CostLDnCad = 0;
            decimal CostEnt=0;
            decimal CostEntCad = 0;
            decimal CostMsc=0;
            decimal CostMscCad = 0;
            decimal SumEnG=0;
            decimal SumEnGCad = 0; 
            decimal SumMscG=0;
            decimal SumMscGCad = 0; 
            decimal SumBus=0;
            decimal SumBusCad = 0;
            decimal SumGui=0;
            decimal SumGuiCad = 0;
            decimal SumFHtl=0;
            decimal SumFHtlCad = 0;
            decimal SumFLDn=0;
            decimal SumFLDnCad = 0;
            decimal SumFEnt=0;
            decimal SumFEntCad = 0;
            decimal SumFSSp=0;
            decimal SumFSSpCad = 0;
            decimal PProfit;
            decimal CostHotelsTaxDriver=0;
            decimal CostHotelsTaxDriverCad = 0;
            decimal CostGuideEntrance = 0;
            decimal CostGuideEntranceCad = 0;
            decimal CostGuideLunchDin = 0;
            decimal CostGuideLunchDinCad = 0;
            decimal CostHotelsTaxGuide = 0;
            decimal CostHotelsTaxGuideCad = 0;
            decimal SSupSum = 0;
            decimal SSupSumCad = 0;
            
            var QuotaEntity = (from quota in _context.tblQuotations where quota.QuotaID == QuotaId && quota.Active == true select quota).SingleOrDefault();
            var QuotaHotelList = (from quotahotel in _context.tblQuotaHotels where quotahotel.QuotaID == QuotaId select quotahotel).ToList();
            var QuotaLunchList = (from quotalunch in _context.tblQuotaLunDins where quotalunch.QuotaID == QuotaId select quotalunch).ToList();
            var QuotaEntranceList = (from quotaentr in _context.tblQuotaEntrances where quotaentr.QuotaID == QuotaId select quotaentr).ToList();
            var QuotaGrpList = (from quotagrp in _context.tblQuotaEntrGrps where quotagrp.QuotaID == QuotaId select quotagrp).ToList();
            var QuotaBusList = (from quotabus in _context.tblQuotaBus where quotabus.QuotaID == QuotaId select quotabus).ToList();
            var QuotaGuideList = (from quotaguide in _context.tblQuotaGuides where quotaguide.QuotaID == QuotaId select quotaguide).ToList();
            var QuotaCostList = (from quotacost in _context.tblQuotaCalcs where quotacost.QuotaID == QuotaId select quotacost).ToList();

            // deleting costs
            foreach(var cost in QuotaCostList)
                _context.tblQuotaCalcs.Remove(cost);
            PaxMin = Convert.ToInt32(QuotaEntity.MinNoOfPax);
            PaxMax = Convert.ToInt32(QuotaEntity.MaxNoOfPax);
            PaxInc = Convert.ToInt32(QuotaEntity.IncNoOfPax); 
            PProfit = Convert.ToDecimal(QuotaEntity.Profit); 
            HFreeR = Convert.ToInt32(QuotaEntity.HFreeRoom);
            HForP = Convert.ToInt32(QuotaEntity.HForPaid);
            OFreeS = Convert.ToInt32(QuotaEntity.OFreeService);
            OForP = Convert.ToInt32(QuotaEntity.OForPaid);
            if (PProfit < 1)
                PProfit = 100 * (1 - PProfit) / PProfit;
            foreach(var hotel in QuotaHotelList)
            {
                decimal tax = Convert.ToDecimal(hotel.Tax);
                decimal RoomTax = Convert.ToDecimal(hotel.Room + (hotel.Room * tax) / 100) + Convert.ToDecimal(hotel.OccTax);
                if (hotel.Currency != StringHelper.CurrencyCad)
                    SSupSum += Convert.ToDecimal(hotel.Nights * RoomTax / 2);
                if (hotel.Currency == StringHelper.CurrencyCad)
                    SSupSumCad += Convert.ToDecimal(hotel.Nights * RoomTax / 2);
                //decimal RoomTax2 = RoomTax / 2;
               // hotel.RoomPP = Math.Round(Convert.ToDecimal(hotel.Nights * (hotel.RoomTax2 + hotel.Breakfast) + hotel.Luggage));
               // CostHtl=CostHtl + Math.Round(total);
                if (hotel.Currency != StringHelper.CurrencyCad)
                    CostHtl = CostHtl + Convert.ToInt32(hotel.Nights) * (RoomTax / 2 + Convert.ToDecimal(hotel.Breakfast)) + Convert.ToDecimal(hotel.Luggage);
                if (hotel.Currency == StringHelper.CurrencyCad)
                    CostHtlCad = CostHtlCad + Convert.ToInt32(hotel.Nights) * (RoomTax / 2 + Convert.ToDecimal(hotel.Breakfast)) + Convert.ToDecimal(hotel.Luggage);
                if (hotel.GuideFlag != null && (bool)hotel.GuideFlag == true && hotel.Currency != StringHelper.CurrencyCad)
                    CostHotelsTaxGuide = CostHotelsTaxGuide + RoomTax * Convert.ToInt32(hotel.Nights) + Convert.ToDecimal(hotel.Luggage);
                if (hotel.GuideFlag != null && (bool)hotel.GuideFlag == true && hotel.Currency == StringHelper.CurrencyCad)
                    CostHotelsTaxGuideCad = CostHotelsTaxGuideCad + RoomTax * Convert.ToInt32(hotel.Nights) + Convert.ToDecimal(hotel.Luggage);
                if (hotel.BGuideFlag != null && (bool)hotel.BGuideFlag == true && hotel.Currency != StringHelper.CurrencyCad)
                    CostHotelsTaxGuide = CostHotelsTaxGuide + Convert.ToDecimal(hotel.Breakfast) * Convert.ToInt32(hotel.Nights);
                if (hotel.BGuideFlag != null && (bool)hotel.BGuideFlag == true && hotel.Currency == StringHelper.CurrencyCad)
                    CostHotelsTaxGuideCad = CostHotelsTaxGuideCad + Convert.ToDecimal(hotel.Breakfast) * Convert.ToInt32(hotel.Nights);
                if (hotel.Currency != StringHelper.CurrencyCad)
                    SumFSSp = SumFSSp + RoomTax / 2 * Convert.ToInt32(hotel.Nights);
                if (hotel.Currency == StringHelper.CurrencyCad)
                    SumFSSpCad = SumFSSpCad + RoomTax / 2 * Convert.ToInt32(hotel.Nights);
                if (hotel.DriverFlag != null && (bool)hotel.DriverFlag == true && hotel.Currency != StringHelper.CurrencyCad)
                    CostHotelsTaxDriver += RoomTax * Convert.ToInt32(hotel.Nights);
                if (hotel.DriverFlag != null && (bool)hotel.DriverFlag == true && hotel.Currency == StringHelper.CurrencyCad)
                    CostHotelsTaxDriverCad += RoomTax * Convert.ToInt32(hotel.Nights);
                if (hotel.FreeFlag != null && (bool)hotel.FreeFlag == true && hotel.Currency != StringHelper.CurrencyCad)
                    SumFHtl += RoomTax * Convert.ToInt32(hotel.Nights);
                if (hotel.FreeFlag != null && (bool)hotel.FreeFlag == true && hotel.Currency == StringHelper.CurrencyCad)
                    SumFHtlCad += RoomTax * Convert.ToInt32(hotel.Nights);
            }
            foreach(var lunch in QuotaLunchList)
            {
                if (lunch.Currency != StringHelper.CurrencyCad)
                    CostLDn+=Convert.ToDecimal(lunch.Cost + lunch.CostD);
                if (lunch.Currency == StringHelper.CurrencyCad)
                    CostLDnCad += Convert.ToDecimal(lunch.Cost + lunch.CostD);
                if (lunch.FreeFlag != null && (bool)lunch.FreeFlag == true && lunch.Currency != StringHelper.CurrencyCad)
                    SumFLDn += Convert.ToDecimal(lunch.Cost);
                if (lunch.FreeFlag != null && (bool)lunch.FreeFlag == true && lunch.Currency == StringHelper.CurrencyCad)
                    SumFLDnCad += Convert.ToDecimal(lunch.Cost);
                if (lunch.FreeFlagD != null && (bool)lunch.FreeFlagD == true && lunch.Currency != StringHelper.CurrencyCad)
                    SumFLDn += Convert.ToDecimal(lunch.CostD);
                if (lunch.FreeFlagD != null && (bool)lunch.FreeFlagD == true && lunch.Currency == StringHelper.CurrencyCad)
                    SumFLDnCad += Convert.ToDecimal(lunch.CostD);
                if (lunch.GuideFlag != null && (bool)lunch.GuideFlag == true && lunch.Currency != StringHelper.CurrencyCad)
                    CostGuideLunchDin += Convert.ToDecimal(lunch.Cost);
                if (lunch.GuideFlag != null && (bool)lunch.GuideFlag == true && lunch.Currency == StringHelper.CurrencyCad)
                    CostGuideLunchDinCad += Convert.ToDecimal(lunch.Cost);
                if (lunch.GuideFlagD != null && (bool)lunch.GuideFlagD == true && lunch.Currency != StringHelper.CurrencyCad)
                    CostGuideLunchDin += Convert.ToDecimal(lunch.CostD);
                if (lunch.GuideFlagD != null && (bool)lunch.GuideFlagD == true && lunch.Currency == StringHelper.CurrencyCad)
                    CostGuideLunchDinCad += Convert.ToDecimal(lunch.CostD);
            }
            foreach(var entrance in QuotaEntranceList)
            {
                if (entrance.Currency != StringHelper.CurrencyCad)
                    CostEnt+=Convert.ToDecimal(entrance.Cost);
                if (entrance.Currency == StringHelper.CurrencyCad)
                    CostEntCad += Convert.ToDecimal(entrance.Cost);
                if (entrance.Currency != StringHelper.CurrencyCad)
                    SumEnG+=Convert.ToDecimal(entrance.CostG);
                if (entrance.Currency == StringHelper.CurrencyCad)
                    SumEnGCad += Convert.ToDecimal(entrance.CostG);
                if (entrance.FreeFlag != null && (bool)entrance.FreeFlag == true && entrance.Currency != StringHelper.CurrencyCad)
                    SumFEnt += Convert.ToDecimal(entrance.Cost);
                if (entrance.FreeFlag != null && (bool)entrance.FreeFlag == true && entrance.Currency == StringHelper.CurrencyCad)
                    SumFEntCad += Convert.ToDecimal(entrance.Cost);
                if (entrance.GuideFlag != null && (bool)entrance.GuideFlag == true && entrance.Currency != StringHelper.CurrencyCad)
                    CostGuideEntrance += Convert.ToDecimal(entrance.Cost);
                if (entrance.GuideFlag != null && (bool)entrance.GuideFlag == true && entrance.Currency == StringHelper.CurrencyCad)
                    CostGuideEntranceCad += Convert.ToDecimal(entrance.Cost);
            }
            foreach(var entgroup in QuotaGrpList)
            {
                if (entgroup.Currency != StringHelper.CurrencyCad)
                    CostMsc+=Convert.ToDecimal(entgroup.Cost);
                if (entgroup.Currency == StringHelper.CurrencyCad)
                    CostMscCad += Convert.ToDecimal(entgroup.Cost);
                if (entgroup.Currency != StringHelper.CurrencyCad)
                    SumMscG+=Convert.ToDecimal(entgroup.CostG);
                if (entgroup.Currency == StringHelper.CurrencyCad)
                    SumMscGCad += Convert.ToDecimal(entgroup.CostG);
            }
            foreach(var bus in QuotaBusList)
            {
                if (bus.Currency != StringHelper.CurrencyCad)
                    SumBus+=Convert.ToDecimal(bus.Cost);
                if (bus.Currency == StringHelper.CurrencyCad)
                    SumBusCad += Convert.ToDecimal(bus.Cost);
            }
            SumBus = SumBus + CostHotelsTaxDriver;
            SumBusCad = SumBusCad + CostHotelsTaxDriverCad;
            // calc guide costs
            // guide
            foreach(var guide in QuotaGuideList)
            {
                if (guide.Currency != StringHelper.CurrencyCad)
                    SumGui+=Convert.ToDecimal(guide.Cost);
                if (guide.Currency == StringHelper.CurrencyCad)
                    SumGuiCad += Convert.ToDecimal(guide.Cost);  
            }
            // entrance
            SumGui += CostGuideEntrance;
            SumGuiCad += CostGuideEntranceCad;
            // lunch dinner
            SumGui += CostGuideLunchDin;
            SumGuiCad += CostGuideLunchDinCad;
            // hotel expanses
            SumGui += CostHotelsTaxGuide;
            SumGuiCad += CostHotelsTaxGuideCad;
           
            PaxCount = PaxMax;
            LinCount = 1;
            tblQuotation Quota = null;
            using (var transaction = _context.Database.BeginTransaction(IsolationLevel.Serializable))
            {
                try
                {
                    Quota = _context.tblQuotations.Where(quota => quota.QuotaID == QuotaId && quota.Active == true).SingleOrDefault();
                    if(Quota != null)
                    {
                        if( Data.SSupAdjs != null)
                            Quota.SSupAdj = Data.SSupAdjs;
                        if (Data.SSupAdjsCad != null)
                            Quota.SSupAdjCad = Data.SSupAdjsCad;
                    }
                    while (PaxCount >= PaxMin)
                    {
                        int coef = PaxCount >= OForP ? 1 : 0;
                        var FAdjs = Data.Rows.Any(row => row.LineNo == LinCount) ? Data.Rows.Where(row => row.LineNo == LinCount).SingleOrDefault().FAdjst : 0;
                        tblQuotaCalc calc = new tblQuotaCalc()
                        {
                            QuotaID = QuotaId,
                            LineNo = (short)LinCount,
                            Pax = (short)PaxCount,
                            HtlPP = CostHtl,
                            LDnPP = CostLDn,
                            EntPP = CostEnt,
                            MscPP = CostMsc,
                            EnGPP = SumEnG / PaxCount,
                            MscGPP = SumMscG / PaxCount,
                            BusPP = SumBus / PaxCount,
                            GuiPP = SumGui / PaxCount,
                            LDnFree = (SumFLDn / PaxCount) * coef * OFreeS,
                            EntFree = (SumFEnt / PaxCount) * coef * OFreeS,
                            PlusFree = 0,
                            PercProfit = (double)PProfit / 100,
                            PlusProfit = 0,
                            Currency = StringHelper.CurrencyUsd,
                            FAdjustment = FAdjs,
                        };
                        LinCount++;
                        tblQuotaCalc calcCad = null;
                        if (!(CostHtlCad == 0 && CostLDnCad == 0 && CostEntCad == 0 && CostMscCad == 0 && SumEnGCad == 0 && SumMscGCad == 0 && SumBusCad == 0 && SumGuiCad == 0 && SumFLDnCad == 0 && SumFEntCad == 0))
                        {
                            calcCad = new tblQuotaCalc()
                           {
                               QuotaID = QuotaId,
                               LineNo = (short)LinCount,
                               Pax = (short)PaxCount,
                               HtlPP = CostHtlCad,
                               LDnPP = CostLDnCad,
                               EntPP = CostEntCad,
                               MscPP = CostMscCad,
                               EnGPP = SumEnGCad / PaxCount,
                               MscGPP = SumMscGCad / PaxCount,
                               BusPP = SumBusCad / PaxCount,
                               GuiPP = SumGuiCad / PaxCount,
                               LDnFree = (SumFLDnCad / PaxCount) * coef * OFreeS,
                               EntFree = (SumFEntCad / PaxCount) * coef * OFreeS,
                               PlusFree = 0,
                               PercProfit = (double)PProfit / 100,
                               PlusProfit = 0,
                               Currency = StringHelper.CurrencyCad,
                           };
                        }
                        double res = PaxCount / (2 * HForP);

                        calc.HtlFree = (SumFHtl / PaxCount) * (decimal)Math.Floor(res) * HFreeR;
                        calc.SSpFree = (SumFSSp / PaxCount) * (decimal)Math.Floor(res) * HFreeR;
                        if (calcCad != null)
                        {
                            calcCad.HtlFree = (SumFHtlCad / PaxCount) * (decimal)Math.Floor(res) * HFreeR;
                            calcCad.SSpFree = (SumFSSpCad / PaxCount) * (decimal)Math.Floor(res) * HFreeR;
                            _context.tblQuotaCalcs.Add(calcCad);
                        }
                        _context.tblQuotaCalcs.Add(calc);

                        PaxCount = PaxCount - PaxInc;
                        LinCount++;
                    }
                    _context.SaveChanges();
                    transaction.Commit();
                }
                catch(Exception ex)
                {
                    logger.Error(ex.ToMessageAndCompleteStacktrace());
                    transaction.Rollback();
                }
            }
            if (PProfit > 1)
            {
                SSupSum = SSupSum + SSupSum / 100 * Convert.ToDecimal(PProfit);
                SSupSumCad = SSupSumCad + SSupSumCad / 100 * Convert.ToDecimal(PProfit);
            }
            if (PProfit < 1 && PProfit > 0)
            {
                SSupSum = SSupSum / Convert.ToDecimal(PProfit);
                SSupSumCad = SSupSumCad / Convert.ToDecimal(PProfit);
            }

            QuotaCostPriceBData.SSupSum = SSupSum;
            QuotaCostPriceBData.SSupSumCad = SSupSumCad;
            try
            {
                QuotaCostPriceBData.Rows = (from quotacalc in _context.tblQuotaCalcs
                                            where quotacalc.QuotaID == QuotaId
                                            orderby quotacalc.LineNo
                                            select new QuotaCostPriceGridRowBData()
                                            {
                                                QuotaID = quotacalc.QuotaID,
                                                BusPP = quotacalc.BusPP,
                                                EnGPP = quotacalc.EnGPP,
                                                EntPP = quotacalc.EntPP,
                                                PercProfit = quotacalc.PercProfit,  
                                                GuiPP = quotacalc.GuiPP,
                                                HtlPP = quotacalc.HtlPP,
                                                LDnPP = quotacalc.LDnPP,
                                                MscGPP = quotacalc.MscGPP,
                                                LineNo = quotacalc.LineNo,
                                                Pax = quotacalc.Pax,           
                                                TotalCost = quotacalc.HtlPP + quotacalc.LDnPP + quotacalc.EntPP + quotacalc.EnGPP + quotacalc.MscPP + quotacalc.MscGPP + quotacalc.BusPP + quotacalc.GuiPP,
                                                MM = quotacalc.Manual,
                                                TotalFree = quotacalc.HtlFree + quotacalc.LDnFree + quotacalc.EntFree,
                                                MscPP = quotacalc.MscPP,
                                                PaxL = quotacalc.Pax,
                                                Currency = quotacalc.Currency,
                                                FAdjst = quotacalc.FAdjustment,
                                            }).ToList();
                foreach (var price in QuotaCostPriceBData.Rows)
                {
                    decimal? givenfreeNoFree = FreeCalcType=="0" ? 0 : price.TotalCost/price.Pax;
                    decimal givenfreeSingleFree = FreeCalcType=="2" ? SumFSSp/price.Pax : 0;
                    decimal givenfreeSingleFreeCad = FreeCalcType == "2" ? SumFSSpCad / price.Pax : 0;
                    price.FreeCost = price.TotalCost - price.TotalFree;
                    price.Sold = price.FreeCost * (1 + Convert.ToDecimal(price.PercProfit));
                    if(price.Currency != StringHelper.CurrencyCad)
                        price.GivenFree = (givenfreeNoFree + givenfreeSingleFree)*Convert.ToDecimal(1+price.PercProfit);
                    if (price.Currency == StringHelper.CurrencyCad)
                        price.GivenFree = (givenfreeNoFree + givenfreeSingleFreeCad) * Convert.ToDecimal(1 + price.PercProfit);
                    if (price.Currency != StringHelper.CurrencyCad)
                        price.GivenFreeP = (givenfreeNoFree + givenfreeSingleFree) * Convert.ToDecimal(price.PercProfit);
                    if (price.Currency == StringHelper.CurrencyCad)
                        price.GivenFreeP = (givenfreeNoFree + givenfreeSingleFreeCad) * Convert.ToDecimal(price.PercProfit);
                    price.GivenSold = price.Sold+price.GivenFree;
                    price.FPrice = price.GivenSold + (price.FAdjst != null ? price.FAdjst : 0);
                    price.Profit = (price.FPrice - price.GivenFree) - price.FreeCost + price.GivenFreeP;
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            if (Quota != null)
            {
                QuotaCostPriceBData.SSupAdjs = Quota.SSupAdj;
                QuotaCostPriceBData.SSupAdjsCad = Quota.SSupAdjCad;
            }
            QuotaCostPriceBData.SSupSumFinal = QuotaCostPriceBData.SSupSum + (QuotaCostPriceBData.SSupAdjs != null ? Convert.ToDecimal(QuotaCostPriceBData.SSupAdjs) : 0);
            QuotaCostPriceBData.SSupSumCadFinal = QuotaCostPriceBData.SSupSumCad + (QuotaCostPriceBData.SSupAdjsCad != null ? Convert.ToDecimal(QuotaCostPriceBData.SSupAdjsCad) : 0);
            return QuotaCostPriceBData;
        }
        public ErrorBInfo SaveQuotaDetails(QuotationDetailsBData QuotaDetails)
        {
            ErrorBInfo ErrInfo = new ErrorBInfo();
            var QuotaEntity = (from quota in _context.tblQuotations where quota.QuotaID == QuotaDetails.QuotaID && quota.Active == true select quota).SingleOrDefault();
            if (QuotaEntity != null)
            {
                QuotaEntity.IsGir = QuotaDetails.IsGir;
                QuotaEntity.Accepted = QuotaDetails.Accepted;
                QuotaEntity.QuotaType = QuotaDetails.SelectedQuotaCountry;
                QuotaEntity.NoOfFlights = QuotaDetails.NoOfFlights;
                QuotaEntity.Region = QuotaDetails.SelectedRegion;
                QuotaEntity.QuotaCode = QuotaDetails.QuotaCode;
                QuotaEntity.CustomerID = QuotaDetails.SelectedCustomerId;
                QuotaEntity.SeasonID = QuotaDetails.SelectedSeason;
                QuotaEntity.QuotaName = QuotaDetails.QuotaName;
                QuotaEntity.QuotaNote = QuotaDetails.QuotaNote;
                QuotaEntity.EmployeeID = QuotaDetails.SelectedEmployeeId;
                QuotaEntity.NoOfNights = QuotaDetails.NoNights;   
                QuotaEntity.ArrivalCity = QuotaDetails.SelectedArrivalCity;
                QuotaEntity.ArrivalDate = QuotaDetails.ArrivalDate;
               // QuotaEntity.QuotaDate = QuotaDetails.QuotaDate;
                QuotaEntity.NoOfMeals = QuotaDetails.NoOfMeals;
                QuotaEntity.DepartureDate = QuotaDetails.DepartureDate;
                QuotaEntity.DepartureCity = QuotaDetails.SelectedDepartureCity;
                QuotaEntity.LastUpDate = DateTime.Now;
                QuotaEntity.MaxNoOfPax = QuotaDetails.MaxNoOfPax;
                QuotaEntity.MinNoOfPax = QuotaDetails.MinNoOfPax;
                QuotaEntity.IncNoOfPax = QuotaDetails.IncNoOfPax;
                QuotaEntity.HFreeRoom = QuotaDetails.HFreeRoom;
                QuotaEntity.HForPaid = QuotaDetails.HForPaid;
                QuotaEntity.FreeCalcType = QuotaDetails.SelectedFreeCalcType;
                QuotaEntity.Profit = QuotaDetails.profit;
                QuotaEntity.OFreeService = QuotaDetails.OFreeService;
                QuotaEntity.OForPaid = QuotaDetails.OForPaid;
                QuotaEntity.ProposalWordDoc = QuotaDetails.Proposal;
            }
            try
            {
                _context.SaveChanges();
            }
            catch(Exception ex)
            {
                ErrInfo.Message = ex.Message;
                ErrInfo.errorCode = 10;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return ErrInfo;
        }
        #region Delete
        public ErrorBInfo RemoveFromGrid(int ItmID, int QuotaId, int LineNo, string TabName)
        {
            ErrorBInfo error = new ErrorBInfo();
            using (var transaction = _context.Database.BeginTransaction(IsolationLevel.Serializable))
            {
                try
                {
                    if (TabName == "Hotel")
                    {
                        var Ent = (from ent in _context.tblQuotaHotels where ent.QuotaHotelID == ItmID select ent).SingleOrDefault();
                        var Ents = (from ent in _context.tblQuotaHotels where ent.QuotaID == QuotaId && ent.LineNo > LineNo select ent).ToList();
                        if (Ent != null)
                        {
                            _context.tblQuotaHotels.Remove(Ent);
                            if (Ents != null && Ents.Count > 0)
                                foreach (var ent in Ents)
                                    ent.LineNo = Convert.ToInt16(ent.LineNo - 1);

                            _context.SaveChanges();
                            transaction.Commit();
                        }
                    }
                    if (TabName == "Lunch")
                    {
                        var Ent = (from ent in _context.tblQuotaLunDins where ent.QuotaLunDinID == ItmID select ent).SingleOrDefault();
                        var Ents = (from ent in _context.tblQuotaLunDins where ent.QuotaID == QuotaId && ent.LineNo > LineNo select ent).ToList();

                        if (Ent != null)
                        {
                            _context.tblQuotaLunDins.Remove(Ent);
                            if (Ents != null && Ents.Count > 0)
                                foreach (var ent in Ents)
                                    ent.LineNo = Convert.ToInt16(ent.LineNo - 1);

                            _context.SaveChanges();
                            transaction.Commit();
                        }
                    }
                    if (TabName == "Entrance")
                    {
                        var Ent = (from ent in _context.tblQuotaEntrances where ent.QuotaEntranceID == ItmID select ent).SingleOrDefault();
                        var Ents = (from ent in _context.tblQuotaEntrances where ent.QuotaID == QuotaId && ent.LineNo > LineNo select ent).ToList();
                        if (Ent != null)
                        {
                            _context.tblQuotaEntrances.Remove(Ent);
                            if (Ents != null && Ents.Count > 0)
                                foreach (var ent in Ents)
                                    ent.LineNo = Convert.ToInt16(ent.LineNo - 1);
                          
                            _context.SaveChanges();
                            transaction.Commit();
                        }
                    }
                    if (TabName == "Misc")
                    {
                        var Ent = (from ent in _context.tblQuotaEntrGrps where ent.QuotaEntrGrpID == ItmID select ent).SingleOrDefault();
                        var Ents = (from ent in _context.tblQuotaEntrGrps where ent.QuotaID == QuotaId && ent.LineNo > LineNo select ent).ToList();

                        if (Ent != null)
                        {
                            _context.tblQuotaEntrGrps.Remove(Ent);
                            if (Ents != null && Ents.Count > 0)
                                foreach (var ent in Ents)
                                    ent.LineNo = Convert.ToInt16(ent.LineNo - 1);

                            _context.SaveChanges();
                            transaction.Commit();
                        }
                    }
                    if (TabName == "Bus")
                    {
                        var Ent = (from ent in _context.tblQuotaBus where ent.QuotaBusID == ItmID select ent).SingleOrDefault();
                        var Ents = (from ent in _context.tblQuotaBus where ent.QuotaID == QuotaId && ent.LineNo > LineNo select ent).ToList();

                        if (Ent != null)
                        {
                            _context.tblQuotaBus.Remove(Ent);
                            if (Ents != null && Ents.Count > 0)
                                foreach (var ent in Ents)
                                    ent.LineNo = Convert.ToInt16(ent.LineNo - 1);

                            _context.SaveChanges();
                            transaction.Commit();
                        }
                    }
                    if (TabName == "Guide")
                    {
                        var Ent = (from ent in _context.tblQuotaGuides where ent.QuotaGuideID == ItmID select ent).SingleOrDefault();
                        var Ents = (from ent in _context.tblQuotaGuides where ent.QuotaID == QuotaId && ent.LineNo > LineNo select ent).ToList();

                        if (Ent != null)
                        {
                            _context.tblQuotaGuides.Remove(Ent);
                            if (Ents != null && Ents.Count > 0)
                                foreach (var ent in Ents)
                                    ent.LineNo = Convert.ToInt16(ent.LineNo - 1);

                            _context.SaveChanges();
                            transaction.Commit();
                        }
                    }
                    if (TabName == "Price")
                    {
                        var Ent = (from ent in _context.tblQuotaCalcs where ent.QuotaID == QuotaId && ent.LineNo == LineNo select ent).SingleOrDefault();
                        if (Ent != null)
                        {
                            _context.tblQuotaCalcs.Remove(Ent);
                            _context.SaveChanges();
                        }
                    }
                }
                catch (Exception ex)
                {
                    error.errorCode = 100;
                    error.Message = ex.Message;
                    logger.Error(ex.ToMessageAndCompleteStacktrace());
                    transaction.Rollback();
                }
            }
            return error;
        }
        public ErrorBInfo UploadProposal(int QuotaId,string PropFile)
        {
            ErrorBInfo Error = new ErrorBInfo();
            var Quota = _context.tblQuotations.Where(quota => quota.QuotaID == QuotaId).SingleOrDefault();
            try
            {
                Quota.ProposalWordDoc = PropFile;
                _context.SaveChanges();
            }
            catch(Exception ex)
            {
                Error.errorCode = 133;
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        #endregion
        #region Advanced Search
        public QuotationAdvancedSearchBData QuotationAdvancedSearchBData()
        {
            var QuotaAdSBData = new QuotationAdvancedSearchBData();
            // country
            QuotaAdSBData.QuotaCountry = (from ctr in _context.tblCountries
                                       where ctr.Active == true
                                       orderby ctr.OrderNum, ctr.CountryName
                                       select new
                                       {
                                           Key = ctr.CountryID,
                                           Value = ctr.CountryName,
                                       }).ToDictionary(o => o.Key, o => o.Value);
            // region
            QuotaAdSBData.Region = (from reg in _context.tblQuotaRegions
                                    where reg.Active == true
                                    orderby reg.Region
                                    select new
                                    {
                                        Key = reg.RegionID,
                                        Value = reg.Region,
                                    }).ToDictionary(o => o.Key, o => o.Value);
            // customer
            QuotaAdSBData.CustomerId = (from cust in _context.tblCustomers
                                     where cust.Active == true
                                     orderby cust.OrganizationName, cust.CustomerCode
                                     select new
                                     {
                                         Key = cust.CustomerID,
                                         Value = cust.OrganizationName,
                                     }).ToDictionary(o => o.Key, o => o.Value);

            // Season
            QuotaAdSBData.Season = (from season in _context.tblSeasons
                                 orderby season.IsCurrent descending
                                 select new
                                 {
                                     Key = season.SeasonID,
                                     Value = season.SeasonDescription,
                                 }).ToDictionary(o => o.Key, o => o.Value);
            // Employee
            QuotaAdSBData.Employee = (from user in _context.Users
                                   from emp in _context.tblEmployees
                                   where user.EmployeeID == emp.EmployeeID && emp.Active == true
                                   orderby emp.FirstName, emp.LastName
                                   select new
                                   {
                                       Key = emp.EmployeeID,
                                       Value = emp.FirstName + " " + emp.LastName,
                                   }).ToDictionary(o => o.Key, o => o.Value);
            // arrival city
            QuotaAdSBData.ArrivalCity = new Dictionary<int, string>();
            QuotaAdSBData.DepartureCity = new Dictionary<int, string>();
            var Cities = _context.tblQuotaCities.Where(city => city.Active == true).Select(obj => new { CId = obj.CityID, city = obj.City }).OrderBy(obj => obj.city).ToList();
            foreach (var city in Cities)
            {
                QuotaAdSBData.ArrivalCity.Add(city.CId, city.city);
                QuotaAdSBData.DepartureCity.Add(city.CId, city.city);
            }
            return QuotaAdSBData;
        }

        public List<QuotaAdvancedSearchGridBData> QuotationAdvancedSearch(QuotationAdvancedSearchBData data)
        {
            List<QuotaAdvancedSearchGridBData> AdSGridData = new List<QuotaAdvancedSearchGridBData>();
            var FilteredQuota = _context.tblQuotations.AsQueryable();
            if (data.Accepted != null)
                FilteredQuota = FilteredQuota.Where(quota => quota.Accepted == data.Accepted);
            if (data.SelectedQuotaType != null)
                FilteredQuota = FilteredQuota.Where(quota => quota.QuotaType == data.SelectedQuotaType);
            if (data.SelectedRegion != null)
                FilteredQuota = FilteredQuota.Where(quota => quota.Region == data.SelectedRegion);
            if (data.ArrivalDateFrom != null)
                FilteredQuota = FilteredQuota.Where(quota => quota.ArrivalDate >= data.ArrivalDateFrom);
            if (data.ArrivalDateTo!= null)
                FilteredQuota = FilteredQuota.Where(quota => quota.ArrivalDate <= data.ArrivalDateTo);
            if (data.QuotaDateFrom != null)
                FilteredQuota = FilteredQuota.Where(quota => quota.QuotaDate >= data.QuotaDateFrom);
            if (data.QuotaDateTo != null)
                FilteredQuota = FilteredQuota.Where(quota => quota.QuotaDate <= data.QuotaDateTo);
            if (data.LastUpdateFrom != null)
                FilteredQuota = FilteredQuota.Where(quota => quota.LastUpDate >= data.LastUpdateFrom);
            if (data.LastUpdateTo != null)
                FilteredQuota = FilteredQuota.Where(quota => quota.LastUpDate <= data.LastUpdateTo);
            if (data.NoNights != null)
                FilteredQuota = FilteredQuota.Where(quota => quota.NoOfNights == data.NoNights);
            if (data.profit != null)
                FilteredQuota = FilteredQuota.Where(quota => quota.Profit == data.profit);
            if (data.QuotaCode != null)
                FilteredQuota = FilteredQuota.Where(quota => quota.QuotaCode.Contains(data.QuotaCode));
            if (data.QuotaName != null)
                FilteredQuota = FilteredQuota.Where(quota => quota.QuotaName.Contains(data.QuotaName));
            if (data.SelectedArrivalCity != null)
                FilteredQuota = FilteredQuota.Where(quota => quota.ArrivalCity==data.SelectedArrivalCity);
            if (data.DepartureCity != null)
                FilteredQuota = FilteredQuota.Where(quota => quota.DepartureCity == data.SelectedDepartureCity);
            if (data.SelectedCustomerId != null)
                FilteredQuota = FilteredQuota.Where(quota => quota.CustomerID == data.SelectedCustomerId);
            if (data.SelectedEmployeeId != null)
                FilteredQuota = FilteredQuota.Where(quota => quota.EmployeeID == data.SelectedEmployeeId);
            if (data.SelectedFreeCalcType != null)
                FilteredQuota = FilteredQuota.Where(quota => quota.FreeCalcType == data.SelectedFreeCalcType);
            if (data.SelectedSeason != null)
                FilteredQuota = FilteredQuota.Where(quota => quota.SeasonID == data.SelectedSeason);

            AdSGridData = (from quota in FilteredQuota
                               select new QuotaAdvancedSearchGridBData 
                                   {
                                       ArrivalDate = quota.ArrivalDate,
                                       LastUpdateDate = quota.LastUpDate,
                                       QuotaCode = quota.QuotaCode,
                                       QuotaID = quota.QuotaID,
                                       QuotaName = quota.QuotaName
                                   }).Take(50).ToList();
                              
            return AdSGridData;
        }

        #endregion
        public Dictionary<int, string> FindKeywordInEntity(string PropName, string keyword)
        {
            Dictionary<int, string> RetVal = new Dictionary<int, string>();
            try
            {
                if (PropName == "HotelDesc")
                {
                    RetVal = _context.tblVendors.Where(vnd => vnd.Active == true && vnd.VendorName.Contains(keyword) && vnd.VendorTypeID == 1).Select(vnd => new { vnd.VendorID, vnd.VendorName }).Take(10).ToDictionary(vnd => vnd.VendorID, vnd => vnd.VendorName);
                    return RetVal;
                }
                if (PropName == "Bus")
                {
                    RetVal = _context.tblVendors.Where(vnd => vnd.Active == true && vnd.VendorName.Contains(keyword) && vnd.VendorTypeID == 3).Select(vnd => new { vnd.VendorID, vnd.VendorName }).Take(10).ToDictionary(vnd => vnd.VendorID, vnd => vnd.VendorName);
                    return RetVal;
                }
                if (PropName == "Guide")
                {
                    RetVal = _context.tblGuides.Where(guide => guide.Active == true && guide.FirstName.Contains(keyword)).Select(guide => new { guide.GuideID, FullName = guide.FirstName + " " + guide.LastName }).Take(10).ToDictionary(guide => guide.GuideID, guide => guide.FullName);
                    return RetVal;
                }
                if (PropName == "LunchDinner")
                {
                    RetVal = _context.tblVendors.Where(vnd => vnd.Active == true && vnd.VendorName.Contains(keyword) && vnd.VendorTypeID == 2).Select(vnd => new { vnd.VendorID, vnd.VendorName }).Take(10).ToDictionary(vnd => vnd.VendorID, vnd => vnd.VendorName);
                    return RetVal;
                }
                if (PropName == "Entrance")
                {
                    RetVal = _context.tblVendors.Where(vnd => vnd.Active == true && vnd.VendorName.Contains(keyword) && vnd.VendorTypeID == 4).Select(vnd => new { vnd.VendorID, vnd.VendorName }).Take(10).ToDictionary(vnd => vnd.VendorID, vnd => vnd.VendorName);
                    return RetVal;
                }
                if (PropName == "GirCustomer")
                {
                    RetVal = _context.tblCustomers.Where(cust => cust.Active == true && cust.OrganizationName.Contains(keyword)).Select(cust => new { cust.CustomerID, cust.OrganizationName }).Take(10).ToDictionary(cust => cust.CustomerID, cust => cust.OrganizationName);
                    return RetVal;
                }
                if (PropName == "VendorNameText")
                {
                    RetVal = _context.tblVendors.Where(vnd => vnd.VendorName.Contains(keyword)).Select(vnd => new { vnd.VendorID, vnd.VendorName }).Take(10).ToDictionary(vnd => vnd.VendorID, vnd => vnd.VendorName);
                    return RetVal;
                }
                if (PropName == "OptionalVendorNameText" || PropName == "OptionalSelectedVendorName")
                {
                    RetVal = _context.tblVendors.Where(vnd => vnd.VendorName.Contains(keyword) && vnd.Active == true).Select(vnd => new { vnd.VendorID, vnd.VendorName }).Take(10).ToDictionary(vnd => vnd.VendorID, vnd => vnd.VendorName);
                    return RetVal;
                }
                if (PropName == "OrganizationNameText")
                {
                    RetVal = _context.tblCustomers.Where(cust => cust.OrganizationName.Contains(keyword)).Select(cust => new { cust.CustomerID, cust.OrganizationName }).Take(10).ToDictionary(cust => cust.CustomerID, cust => cust.OrganizationName);
                    return RetVal;
                }
                if (PropName == "CustomerCodeText")
                {
                    RetVal = _context.tblCustomers.Where(cust => cust.CustomerCode.Contains(keyword)).Select(cust => new { cust.CustomerID, cust.CustomerCode }).Take(10).ToDictionary(cust => cust.CustomerID, cust => cust.CustomerCode);
                    return RetVal;
                }
                if (PropName == "SeasonCodeText")
                {
                    RetVal = _context.tblSeasons.Where(season => season.SeasonCode.Contains(keyword)).Select(season => new { season.SeasonID, season.SeasonCode }).Take(10).ToDictionary(season => season.SeasonID, season => season.SeasonCode);
                    return RetVal;
                }
                if (PropName == "SeasonDescriptionText")
                {
                    RetVal = _context.tblSeasons.Where(season => season.SeasonDescription.Contains(keyword)).Select(season => new { season.SeasonID, season.SeasonDescription }).Take(10).ToDictionary(season => season.SeasonID, season => season.SeasonDescription);
                    return RetVal;
                }
                if (PropName == "GuideNameText")
                {
                    RetVal = _context.tblGuides.Where(guid => guid.FirstName.Contains(keyword)).Select(guid => new { guid.GuideID, guid.FirstName }).Take(10).ToDictionary(guid => guid.GuideID, guid => guid.FirstName);
                    return RetVal;
                }
                if (PropName == "GuideLastNameText")
                {
                    RetVal = _context.tblGuides.Where(guid => guid.LastName.Contains(keyword)).Select(guid => new { guid.GuideID, guid.LastName }).Take(10).ToDictionary(guid => guid.GuideID, guid => guid.LastName);
                    return RetVal;
                }
                if (PropName == "CompanyCodeText")
                {
                    RetVal = _context.tblCompanies.Where(cmp => cmp.CompanyCode.Contains(keyword)).Select(cmp => new { cmp.CompanyID, cmp.CompanyCode }).Take(10).ToDictionary(cmp => cmp.CompanyID, cmp => cmp.CompanyCode);
                    return RetVal;
                }
                if (PropName == "CompanyNameText")
                {
                    RetVal = _context.tblCompanies.Where(cmp => cmp.CompanyName.Contains(keyword)).Select(cmp => new { cmp.CompanyID, cmp.CompanyName }).Take(10).ToDictionary(cmp => cmp.CompanyID, cmp => cmp.CompanyName);
                    return RetVal;
                }
                if (PropName == "BankCodeText")
                {
                    RetVal = _context.tblBanks.Where(bnk => bnk.BankCode.Contains(keyword)).Select(bnk => new { bnk.BankID, bnk.BankCode }).Take(10).ToDictionary(bnk => bnk.BankID, bnk => bnk.BankCode);
                    return RetVal;
                }
                if (PropName == "BankNameText")
                {
                    RetVal = _context.tblBanks.Where(bnk => bnk.BankName.Contains(keyword)).Select(bnk => new { bnk.BankID, bnk.BankName }).Take(10).ToDictionary(bnk => bnk.BankID, bnk => bnk.BankName);
                    return RetVal;
                }
                if (PropName == "CardNumberText")
                {
                    RetVal = _context.tblCards.Where(card => card.CardNumber.Contains(keyword)).Select(card => new { card.CardID, card.CardNumber }).Take(10).ToDictionary(card => card.CardID, card => card.CardNumber);
                    return RetVal;
                }
                if (PropName == "PlaceHolderNameText")
                {
                    RetVal = _context.tblCards.Where(card => card.PlaceHolderName.Contains(keyword)).Select(card => new { card.CardID, card.PlaceHolderName }).Take(10).ToDictionary(card => card.CardID, card => card.PlaceHolderName);
                    return RetVal;
                }
                if (PropName == "PaymentTypeCodeText")
                {
                    RetVal = _context.tblPayTypes.Where(pType => pType.PayTypeCode.Contains(keyword)).Select(pType => new { pType.PayTypeID, pType.PayTypeCode }).Take(10).ToDictionary(pType => pType.PayTypeID, pType => pType.PayTypeCode);
                    return RetVal;
                }
                if (PropName == "PaymentTypeDescriptionText")
                {
                    RetVal = _context.tblPayTypes.Where(pType => pType.PayTypeDesc.Contains(keyword)).Select(pType => new { pType.PayTypeID, pType.PayTypeDesc }).Take(10).ToDictionary(pType => pType.PayTypeID, pType => pType.PayTypeDesc);
                    return RetVal;
                }
                if (PropName == "ServiceTypeCodeText")
                {
                    RetVal = _context.tblServiceTypes.Where(sType => sType.ServiceTypeCode.Contains(keyword)).Select(sType => new { sType.ServiceTypeID, sType.ServiceTypeCode }).Take(10).ToDictionary(sType => sType.ServiceTypeID, sType => sType.ServiceTypeCode);
                    return RetVal;
                }
                if (PropName == "ServiceTypeDescriptionText")
                {
                    RetVal = _context.tblServiceTypes.Where(sType => sType.ServiceTypeDesc.Contains(keyword)).Select(sType => new { sType.ServiceTypeID, sType.ServiceTypeDesc }).Take(10).ToDictionary(sType => sType.ServiceTypeID, sType => sType.ServiceTypeDesc);
                    return RetVal;
                }
                if (PropName == "ServiceCodeText")
                {
                    RetVal = _context.tblServices.Where(service => service.ServiceCode.Contains(keyword)).Select(service => new { service.ServiceID, service.ServiceCode }).Take(10).ToDictionary(service => service.ServiceID, service => service.ServiceCode);
                    return RetVal;
                }
                if (PropName == "ServiceDescriptionText")
                {
                    RetVal = _context.tblServices.Where(service => service.ServiceDesc.Contains(keyword)).Select(service => new { service.ServiceID, service.ServiceDesc }).Take(10).ToDictionary(service => service.ServiceID, service => service.ServiceDesc);
                    return RetVal;
                }
                if (PropName == "VendorTypeCodeText")
                {
                    RetVal = _context.tblVendorTypes.Where(vType => vType.VendorTypeCode.Contains(keyword)).Select(vType => new { vType.VendorTypeID, vType.VendorTypeCode }).Take(10).ToDictionary(vType => vType.VendorTypeID, vType => vType.VendorTypeCode);
                    return RetVal;
                }
                if (PropName == "VendorTypeDescriptionText")
                {
                    RetVal = _context.tblVendorTypes.Where(vType => vType.VendorTypeDesc.Contains(keyword)).Select(vType => new { vType.VendorTypeID, vType.VendorTypeDesc }).Take(10).ToDictionary(vType => vType.VendorTypeID, vType => vType.VendorTypeDesc);
                    return RetVal;
                }
                if (PropName == "DetailTypeDescriptionText")
                {
                    RetVal = _context.tblDetailTypes.Where(dType => dType.DetailTypeDesc.Contains(keyword)).Select(dType => new { dType.DetailTypeID, dType.DetailTypeDesc }).Take(10).ToDictionary(dType => dType.DetailTypeID, dType => dType.DetailTypeDesc);
                    return RetVal;
                }
                if (PropName == "DetailCodeText")
                {
                    RetVal = _context.tblDetails.Where(detail => detail.DetailCode.Contains(keyword)).Select(detail => new { detail.DetailID, detail.DetailCode }).Take(10).ToDictionary(detail => detail.DetailID, detail => detail.DetailCode);
                    return RetVal;
                }
                if (PropName == "DetailDescriptionText")
                {
                    RetVal = _context.tblDetails.Where(detail => detail.DetailDescription.Contains(keyword)).Select(detail => new { detail.DetailID, detail.DetailDescription }).Take(10).ToDictionary(detail => detail.DetailID, detail => detail.DetailDescription);
                    return RetVal;
                }
                if (PropName == "RegionCodeText")
                {
                    RetVal = _context.tblRegions.Where(reg => reg.RegionCode.Contains(keyword)).Select(reg => new { reg.RegionID, reg.RegionCode }).Take(10).ToDictionary(reg => reg.RegionID, reg => reg.RegionCode);
                    return RetVal;
                }
                if (PropName == "RegionDescriptionText")
                {
                    RetVal = _context.tblRegions.Where(reg => reg.RegionDesc.Contains(keyword)).Select(reg => new { reg.RegionID, reg.RegionDesc }).Take(10).ToDictionary(reg => reg.RegionID, reg => reg.RegionDesc);
                    return RetVal;
                }
                if (PropName == "AreaCodeText")
                {
                    RetVal = _context.tblAreas.Where(area => area.AreaCode.Contains(keyword)).Select(area => new { area.AreaID, area.AreaCode }).Take(10).ToDictionary(area => area.AreaID, area => area.AreaCode);
                    return RetVal;
                }
                if (PropName == "AreaDescriptionText")
                {
                    RetVal = _context.tblAreas.Where(area => area.AreaDesc.Contains(keyword)).Select(area => new { area.AreaID, area.AreaDesc }).Take(10).ToDictionary(area => area.AreaID, area => area.AreaDesc);
                    return RetVal;
                }
                if (PropName == "StateIDText")
                {
                    RetVal = _context.tblStates.Where(state => state.StateID.Contains(keyword)).Select(state => new { state.StID, state.StateID }).Take(10).ToDictionary(state => state.StID, state => state.StateID);
                    return RetVal;
                }
                if (PropName == "StateNameText")
                {
                    RetVal = _context.tblStates.Where(state => state.StateName.Contains(keyword)).Select(state => new { state.StID, state.StateName }).Take(10).ToDictionary(state => state.StID, state => state.StateName);
                    return RetVal;
                }
                if (PropName == "CountryCodeText")
                {
                    RetVal = _context.tblCountries.Where(cnt => cnt.CountryCode.Contains(keyword)).Select(cnt => new { cnt.CountryID, cnt.CountryCode }).Take(10).ToDictionary(cnt => cnt.CountryID, cnt => cnt.CountryCode);
                    return RetVal;
                }
                if (PropName == "CountryNameText")
                {
                    RetVal = _context.tblCountries.Where(cnt => cnt.CountryName.Contains(keyword)).Select(cnt => new { cnt.CountryID, cnt.CountryName }).Take(10).ToDictionary(cnt => cnt.CountryID, cnt => cnt.CountryName);
                    return RetVal;
                }
                if (PropName == "NationalityText")
                {
                    RetVal = _context.tblCountries.Where(cnt => cnt.Nationality.Contains(keyword)).Select(cnt => new { cnt.CountryID, cnt.Nationality }).Take(10).ToDictionary(cnt => cnt.CountryID, cnt => cnt.Nationality);
                    return RetVal;
                }
                if (PropName == "RegionText")
                {
                    RetVal = _context.tblQuotaRegions.Where(qRegion => qRegion.Region.Contains(keyword)).Select(qRegion => new { qRegion.RegionID, qRegion.Region }).Take(10).ToDictionary(qRegion => qRegion.RegionID, qRegion => qRegion.Region);
                    return RetVal;
                }
                if (PropName == "TechTypeCodeText")
                {
                    RetVal = _context.tblTechDetailTypes.Where(tdType => tdType.TechTypeCode.Contains(keyword)).Select(tdType => new { tdType.TechTypeID, tdType.TechTypeCode }).Take(10).ToDictionary(tdType => tdType.TechTypeID, tdType => tdType.TechTypeCode);
                    return RetVal;
                }
                if (PropName == "TechTypeDescriptionText")
                {
                    RetVal = _context.tblTechDetailTypes.Where(tdType => tdType.TechTypeDesc.Contains(keyword)).Select(tdType => new { tdType.TechTypeID, tdType.TechTypeDesc }).Take(10).ToDictionary(tdType => tdType.TechTypeID, tdType => tdType.TechTypeDesc);
                    return RetVal;
                }
                if (PropName == "CityText")
                {
                    RetVal = _context.tblCities.Where(City => City.City.Contains(keyword)).Select(City => new { City.CityID, City.City }).Take(10).ToDictionary(City => City.CityID, City => City.City);
                    return RetVal;
                }
                if (PropName == "StatusDescText")
                {
                    RetVal = _context.tblVConfirmStatus.Where(VCS => VCS.StatusDesc.Contains(keyword)).Select(VCS => new { VCS.StatusID, VCS.StatusDesc }).Take(10).ToDictionary(VCS => VCS.StatusID, VCS => VCS.StatusDesc);
                    return RetVal;
                }
                if (PropName == "QuotaCodeText")
                {
                    RetVal = _context.tblQuotations.Where(Quota => Quota.QuotaCode.Contains(keyword)).Select(Quota => new { Quota.QuotaID, Quota.QuotaCode }).Take(10).ToDictionary(Quota => Quota.QuotaID, Quota => Quota.QuotaCode);
                    return RetVal;
                }
                if (PropName == "QuotaNameText")
                {
                    RetVal = _context.tblQuotations.Where(Quota => Quota.QuotaName.Contains(keyword)).Select(Quota => new { Quota.QuotaID, Quota.QuotaName }).Take(10).ToDictionary(Quota => Quota.QuotaID, Quota => Quota.QuotaName);
                    return RetVal;
                }
                if (PropName == "ReportSelectedVendorName")
                {
                    RetVal = _context.tblVendors.Where(vnd => vnd.VendorName.Contains(keyword) && vnd.Active == true).Select(vnd => new { vnd.VendorID, vnd.VendorName }).Take(10).ToDictionary(vnd => vnd.VendorID, vnd => vnd.VendorName);
                    return RetVal;
                }
                if (PropName == "ReportSelectedGuideName")
                {
                    RetVal = _context.tblGuides.Where(guide => (guide.FirstName + " " + guide.LastName).Contains(keyword) && guide.Active == true).Select(guide => new { guide.GuideID, guide.FirstName, guide.LastName }).Take(10).ToDictionary(guide => guide.GuideID, guide => guide.FirstName + " " + guide.LastName);
                    return RetVal;
                }
                if (PropName == "ReportSelectedCustomerOrgName")
                {
                    RetVal = _context.tblCustomers.Where(cust => cust.OrganizationName.Contains(keyword) && cust.Active == true).Select(cust => new { cust.CustomerID, cust.OrganizationName }).Take(10).ToDictionary(cust => cust.CustomerID, cust => cust.OrganizationName);
                    return RetVal;
                }
                if (PropName == "VendorName")
                {
                    RetVal = _context.tblVendors.Where(vnd => vnd.VendorName.Contains(keyword) && vnd.Active == true).Select(vnd => new { vnd.VendorID, vnd.VendorName }).Take(10).ToDictionary(vnd => vnd.VendorID, vnd => vnd.VendorName);
                    return RetVal;
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return RetVal;
        }
        public List<QuotaFindHotelBData> FindHotelWithKeyword(string HotelName)
        {
            List<QuotaFindHotelBData> HotelData = new List<QuotaFindHotelBData>();
         //   HotelData = from htl in _context.tblVendors
            return HotelData;
        }
        public QuotaVendorContractsBData LoadVendorContract(int VendorID, int SeasonID)
        {
            QuotaVendorContractsBData VendorContract = new QuotaVendorContractsBData();
            var Contract = _context.tblContracts.Where(Contr=>Contr.VendorID == VendorID && Contr.SeasonID == SeasonID).SingleOrDefault();
            if(Contract != null)
            {
                VendorContract.Tax = Contract.Tax;
                VendorContract.OccTax = Contract.OccTax;
                VendorContract.Luggage = Contract.Luggage;
                VendorContract.Curr = Contract.Currency;
            }
            VendorContract.Rows = new List<QuotaVendorContractsRowBData>();
            VendorContract.Rows = (from contractdet in _context.tblContractDetails
                                   from serv in _context.tblServices
                                   from servtype in _context.tblServiceTypes
                                   where contractdet.SeasonID == SeasonID 
                                   && contractdet.VendorID == VendorID
                                   && serv.Active == true
                                   && contractdet.ServiceID == serv.ServiceID
                                   && servtype.Active == true
                                   && serv.ServiceTypeID == servtype.ServiceTypeID
                                   orderby contractdet.LineNo
                                   select new QuotaVendorContractsRowBData()
                                   {
                                       Description = serv.ServiceDesc,
                                       Curr = contractdet.Currency,
                                       Service = servtype.ServiceTypeCode + " - " + serv.ServiceCode,
                                       Price = contractdet.Price,
                                       ContractStart = contractdet.StartDate,
                                       ContractEnd = contractdet.EndDate,
                                       LineNo = contractdet.LineNo,
                                   }).ToList();
                                       
            return VendorContract;
        }
        #region Gir Non Gir Groups
        public int CreateNonGirGroup()
        {
            int GroupID = 1;
            if(_context.tblGroups.Count() > 0)
                GroupID = _context.tblGroups.Max(gr => gr.GroupID) + 1;

            return GroupID;
        }
        public ErrorBInfo SaveNonGirGroup(QuotationGroupBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
           
            Data.CompanyID = 1;
            tblGroup NewGroup = new tblGroup()
            {
                GroupID = Data.GroupID,
                QuotaID = Data.QuotaID,
                GroupCode = Data.GroupCode,
                CustomerID = Data.CustomerID,
                SeasonID = Data.SeasonID,
                GroupName = Data.GroupName,
                NoOfPerson = Data.GroupEstPax,
                Rooms = Data.GroupEstRoom,
                CompanyID = Data.CompanyID,
                IsGir = false,
            };
            try
            {
                _context.tblGroups.Add(NewGroup);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.Message = ex.Message;
                Error.errorCode = 1010;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public QuotationGirGroupGridBData CreateGirNonGirGroup(int QuotaID, bool Gir, string UserName)
        {
            IQueryable<tblGroup> Group = _context.tblGroups.AsQueryable();
            Group = Group.Where(gr => gr.QuotaID == QuotaID);
            return InitQuotaGirGroupGridBData(Group, Gir, UserName);
        }
        public QuotationGirGroupGridBData SaveGirNonGirGroup(QuotationGirGroupGridBData Data, bool Gir, string UserName)
        {
            ErrorBInfo Error = new ErrorBInfo();
            using (var transaction = _context.Database.BeginTransaction(IsolationLevel.Serializable))
            {
                try
                {
                    var GroupIDList = Data.Rows.Select(grp => grp.GroupID).ToList();
                    var ChangedGroups = _context.tblGroups.Where(grp => GroupIDList.Contains(grp.GroupID)).ToList();
                    foreach(var group in ChangedGroups)
                    {
                        var rowData = Data.Rows.Where(row => row.GroupID == group.GroupID).SingleOrDefault();
                        if(rowData != null)
                        {
                            if (group.ArrivalDate != rowData.ArrivalDate)
                            {
                                group.IsPushed = false;
                            }
                            group.GroupCode = rowData.GroupCode;
                            group.GroupName = rowData.GroupName;
                            group.ArrivalDate = rowData.ArrivalDate;
                            group.DepartureDate = rowData.DepartureDate;
                            group.NoOfPerson = rowData.Pax;
                            group.Rooms = rowData.Room;
                            group.StatusID = rowData.SelStatusID;
                            group.CountryID = rowData.SelCountryID;
                        }
                    }
                    _context.SaveChanges();
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    Error.Message = ex.Message;
                    Error.errorCode = 1020;
                    logger.Error(ex.ToMessageAndCompleteStacktrace());
                    transaction.Rollback();
                }
            }
            IQueryable<tblGroup> Group = _context.tblGroups.AsQueryable();
            Group = Group.Where(gr => gr.QuotaID == Data.QuotaID);
            var GirGroupBData = InitQuotaGirGroupGridBData(Group, Gir, UserName);
            GirGroupBData.Error = Error;
            return GirGroupBData;
        }
        public QuotationGirGroupGridBData SaveGirGroupInvoice(QuotationGirGroupGridBData Data, string UserName)
        {
            ErrorBInfo Error = new ErrorBInfo();
            using (var transaction = _context.Database.BeginTransaction(IsolationLevel.Serializable))
            {
                try
                {
                    var GroupIDList = Data.InvRows.Select(grp => grp.InvGroupID).ToList();
                    var CustIDList = Data.InvRows.GroupBy(cus=>cus.InvCustomerID).Select(cus => cus.Key).ToList();
                    var ChangedGirDet = _context.tblGirGroupDetails.Where(det => GroupIDList.Contains(det.GroupID) && CustIDList.Contains(det.CustomerID)).ToList();
                    var ChangedGirInvoices = _context.tblGirGroupInvoices.Where(GrInv => GroupIDList.Contains(GrInv.GroupID) && CustIDList.Contains(GrInv.CustomerID)).ToList();
                    foreach (var GirDet in ChangedGirDet)
                    {
                        var rowData = Data.InvRows.Where(row => row.InvGroupID == GirDet.GroupID && row.InvCustomerID == GirDet.CustomerID).SingleOrDefault();
                        if (rowData != null)
                        {
                           // GirDet.CustomerID = rowData.InvCustomerID;
                            if (GirDet.PricePerPerson != rowData.InvPricePerPerson)
                            {
                                GirDet.PricePerPerson = rowData.InvPricePerPerson;
                                var InvPerPersUS = ChangedGirInvoices.Where(InvGr => InvGr.CustomerID == rowData.InvCustomerID && InvGr.GroupID == rowData.InvGroupID && InvGr.Currency == "USD" && InvGr.Description == "Per Person").FirstOrDefault();
                                if(InvPerPersUS != null)
                                {
                                    InvPerPersUS.Price = Convert.ToDecimal(rowData.InvPricePerPerson);
                                    InvPerPersUS.PriceChange = rowData.InvPricePerPerson;
                                    if(InvPerPersUS.DebCre == true)
                                        InvPerPersUS.Credit = Convert.ToDecimal(InvPerPersUS.PriceChange) * InvPerPersUS.Quantity;
                                    if (InvPerPersUS.DebCre == false)
                                        InvPerPersUS.Debit = Convert.ToDecimal(InvPerPersUS.PriceChange) * InvPerPersUS.Quantity;
                                }      
                            }
                            if (GirDet.PriceFullBoard != rowData.InvPriceFBoard)
                            {
                                GirDet.PriceFullBoard = rowData.InvPriceFBoard;
                                var InvFullBUS = ChangedGirInvoices.Where(InvGr => InvGr.CustomerID == rowData.InvCustomerID && InvGr.GroupID == rowData.InvGroupID && InvGr.Currency == "USD" && InvGr.Description == "Full Board").FirstOrDefault();
                                if (InvFullBUS != null)
                                {
                                    InvFullBUS.Price = Convert.ToDecimal(rowData.InvPriceFBoard);
                                    InvFullBUS.PriceChange = rowData.InvPriceFBoard;
                                    if (InvFullBUS.DebCre == true)
                                        InvFullBUS.Credit = Convert.ToDecimal(InvFullBUS.PriceChange) * InvFullBUS.Quantity;
                                    if (InvFullBUS.DebCre == false)
                                        InvFullBUS.Debit = Convert.ToDecimal(InvFullBUS.PriceChange) * InvFullBUS.Quantity;
                                }
                            }
                            if (GirDet.PriceSingleSupp != rowData.InvPriceSingleSup)
                            {
                                GirDet.PriceSingleSupp = rowData.InvPriceSingleSup;
                                var InvSSupUS = ChangedGirInvoices.Where(InvGr => InvGr.CustomerID == rowData.InvCustomerID && InvGr.GroupID == rowData.InvGroupID && InvGr.Currency == "USD" && InvGr.Description == "Single Supplement").FirstOrDefault();
                                if (InvSSupUS != null)
                                {
                                    InvSSupUS.Price = Convert.ToDecimal(rowData.InvPriceSingleSup);
                                    InvSSupUS.PriceChange = rowData.InvPriceSingleSup;
                                    if (InvSSupUS.DebCre == true)
                                        InvSSupUS.Credit = Convert.ToDecimal(InvSSupUS.PriceChange) * InvSSupUS.Quantity;
                                    if (InvSSupUS.DebCre == false)
                                        InvSSupUS.Debit = Convert.ToDecimal(InvSSupUS.PriceChange) * InvSSupUS.Quantity;
                                }
                            }
                            if (GirDet.PricePerPersonCad != rowData.InvPricePerPersonCad)
                            {
                                GirDet.PricePerPersonCad = rowData.InvPricePerPersonCad;
                                var InvPerPersCad = ChangedGirInvoices.Where(InvGr => InvGr.CustomerID == rowData.InvCustomerID && InvGr.GroupID == rowData.InvGroupID && InvGr.Currency == "CAD" && InvGr.Description == "Per Person").FirstOrDefault();
                                if (InvPerPersCad != null)
                                {
                                    InvPerPersCad.Price = Convert.ToDecimal(rowData.InvPricePerPersonCad);
                                    InvPerPersCad.PriceChange = rowData.InvPricePerPersonCad;
                                    if (InvPerPersCad.DebCre == true)
                                        InvPerPersCad.Credit = Convert.ToDecimal(InvPerPersCad.PriceChange) * InvPerPersCad.Quantity;
                                    if (InvPerPersCad.DebCre == false)
                                        InvPerPersCad.Debit = Convert.ToDecimal(InvPerPersCad.PriceChange) * InvPerPersCad.Quantity;
                                }
                            }
                            if (GirDet.PriceFullBoardCad != rowData.InvPriceFBoardCad)
                            {
                                GirDet.PriceFullBoardCad = rowData.InvPriceFBoardCad;
                                var InvFullBCad= ChangedGirInvoices.Where(InvGr => InvGr.CustomerID == rowData.InvCustomerID && InvGr.GroupID == rowData.InvGroupID && InvGr.Currency == "CAD" && InvGr.Description == "Full Board").FirstOrDefault();
                                if (InvFullBCad != null)
                                {
                                    InvFullBCad.Price = Convert.ToDecimal(rowData.InvPriceFBoardCad);
                                    InvFullBCad.PriceChange = rowData.InvPriceFBoardCad;
                                    if (InvFullBCad.DebCre == true)
                                        InvFullBCad.Credit = Convert.ToDecimal(InvFullBCad.PriceChange) * InvFullBCad.Quantity;
                                    if (InvFullBCad.DebCre == false)
                                        InvFullBCad.Debit = Convert.ToDecimal(InvFullBCad.PriceChange) * InvFullBCad.Quantity;
                                }
                            }
                            if (GirDet.PriceSingleSuppCad != rowData.InvPriceSingleSupCad)
                            {
                                GirDet.PriceSingleSuppCad = rowData.InvPriceSingleSupCad;
                                var InvSSupCad = ChangedGirInvoices.Where(InvGr => InvGr.CustomerID == rowData.InvCustomerID && InvGr.GroupID == rowData.InvGroupID && InvGr.Currency == "CAD" && InvGr.Description == "Single Supplement").FirstOrDefault();
                                if (InvSSupCad != null)
                                {
                                    InvSSupCad.Price = Convert.ToDecimal(rowData.InvPriceSingleSupCad);
                                    InvSSupCad.PriceChange = rowData.InvPriceSingleSupCad;
                                    if (InvSSupCad.DebCre == true)
                                        InvSSupCad.Credit = Convert.ToDecimal(InvSSupCad.PriceChange) * InvSSupCad.Quantity;
                                    if (InvSSupCad.DebCre == false)
                                        InvSSupCad.Debit = Convert.ToDecimal(InvSSupCad.PriceChange) * InvSSupCad.Quantity;
                                }
                            }   
                        }
                    }
                    _context.SaveChanges();
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    Error.Message = ex.Message;
                    Error.errorCode = 1020;
                    logger.Error(ex.ToMessageAndCompleteStacktrace());
                    transaction.Rollback();
                }
            }
            IQueryable<tblGroup> Group = _context.tblGroups.AsQueryable();
            Group = Group.Where(gr => gr.QuotaID == Data.QuotaID);
            var GirGroupBData = InitQuotaGirGroupGridBData(Group, true, UserName);
            GirGroupBData.Error = Error;
            return GirGroupBData;
        }
        public QuotationGirGroupGridBData NewGirNonGirGroup(QuotationGirGroupGridBData Data, bool Gir, string UserName)
        {
            ErrorBInfo Error = new ErrorBInfo();
            var Quota = _context.tblQuotations.Where(quota => quota.QuotaID == Data.QuotaID).SingleOrDefault();
            try
            {
                tblGroup NewGroup = new tblGroup()
                {
                    IsPushed = false,
                    GroupID = Data.GroupID,
                    QuotaID = Data.QuotaID,
                    GroupCode = Data.GroupCode,
                    CustomerID = Data.SelCustomerID,
                    SeasonID = Data.SeasonID,
                    GroupName = Data.GroupName,
                    CompanyID = 1,
                    IsGir = Gir,
                    AsGir = Data.AsGir,
                    EmployeeID = Quota != null ? Quota.EmployeeID : (int?)null,
                    ArrivalCity = Quota.ArrivalCity,
                    DepartureCity = Quota.DepartureCity,
                    StatusID = Data.SelStatusID,
                    CountryID = Data.SelCountryID,
                    Rooms = Data.Room,
                    NoOfPerson = Data.Pax,
                    ArrivalDate = Data.ArrivalDate,
                    DepartureDate = Data.NoNights != null && Data.ArrivalDate != null ? Convert.ToDateTime(Data.ArrivalDate).AddDays(Convert.ToInt16(Data.NoNights)) : (DateTime?)null,
                    HotelEmpID = Data.SelHotelEmpID,
                    AttrEmpID = Data.SelAttrEmpID,
                    MiscEmpID = Data.SelAttrEmpID,
                    BusEmpID = Data.SelBusEmpID,
                };
                _context.tblGroups.Add(NewGroup);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.Message = GetExceptionMessages(ex);
                Error.errorCode = 1010;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            IQueryable<tblGroup> Group = _context.tblGroups.AsQueryable();
            Group = Group.Where(gr => gr.QuotaID == Data.QuotaID);
            var GirGroupBData = InitQuotaGirGroupGridBData(Group, Gir, UserName);
            GirGroupBData.Error = Error;
            return GirGroupBData;
        }

        public QuotationGirGroupGridBData NewGirNonGirGroupPushOperation(int GroupID, bool Gir, string UserName)
        {
            var CurrentGroup = _context.tblGroups.Where(grp => grp.GroupID == GroupID).SingleOrDefault();
            var Error = GroupPushOperation(CurrentGroup);
            IQueryable<tblGroup> Group = _context.tblGroups.AsQueryable();
            Group = CurrentGroup != null ? Group.Where(gr => gr.QuotaID == CurrentGroup.QuotaID) : Group.Where(gr => gr.GroupID == GroupID);
            var GirGroupBData = InitQuotaGirGroupGridBData(Group, Gir, UserName);
            GirGroupBData.Error = Error;
            return GirGroupBData;
        }
        public QuotationGirGroupGridBData NewGirGroupCustomer(QuotationGirGroupGridBData Data, string UserName)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                tblGirGroupDetail NewGirDet = new tblGirGroupDetail()
                {
                    GroupID = Data.InvGroupID,
                    CustomerID = Data.InvCustomerID,
                    PricePerPerson = Data.InvPricePerPerson,
                    PriceFullBoard = Data.InvPriceFBoard,
                    PriceSingleSupp = Data.InvPriceSingleSup,
                    PricePerPersonCad = Data.InvPricePerPersonCad,
                    PriceFullBoardCad = Data.InvPriceFBoardCad,
                    PriceSingleSuppCad = Data.InvPriceSingleSupCad,
                };
                _context.tblGirGroupDetails.Add(NewGirDet);
                _context.SaveChanges();
            }
            catch(Exception ex)
            {
                Error.errorCode = 8976;
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            IQueryable<tblGroup> Group = _context.tblGroups.AsQueryable();
            Group = Group.Where(gr => gr.QuotaID == Data.QuotaID);
            var GirGroupBData = InitQuotaGirGroupGridBData(Group, true, UserName);
            GirGroupBData.Error = Error;
            return GirGroupBData;
        }

        #endregion
        #region private methods
        private ErrorBInfo GroupPushOperation(tblGroup CurrentGroup)
        {
            ErrorBInfo Error = new ErrorBInfo();
            using (var transaction = _context.Database.BeginTransaction(IsolationLevel.Serializable))
            {
                if (CurrentGroup != null)
                {
                    // Bus, Guide
                    var QuotaGuide = _context.tblQuotaGuides.Where(guide => guide.QuotaID == CurrentGroup.QuotaID).OrderBy(guide => guide.LineNo).Select(
                                   guide => new { GuideId = guide.GuideID, MFlag = guide.GuideMFlag, Desc = guide.Description, LineNo = guide.LineNo, Day = guide.Day, DayQty = guide.DayQty, Curr = guide.Currency, Rate = guide.Rate, ServiceId = guide.ServiceID }).ToList();

                    var QuotaBus = _context.tblQuotaBus.Where(bus => bus.QuotaID == CurrentGroup.QuotaID).OrderBy(bus => bus.LineNo).Select(
                                   bus => new { VendorId = bus.VendorID, Mflag = bus.BusMFlag, Desc = bus.Description, LineNo = bus.LineNo, Day = bus.Day, DayQty = bus.DayQty, Curr = bus.Currency, Rate = bus.Rate, ServiceId = bus.ServiceID }).ToList();

                    // Attraction, Restaurants
                    var QuotaAttr = _context.tblQuotaEntrances.Where(entr => entr.QuotaID == CurrentGroup.QuotaID).OrderBy(entr => entr.LineNo).Select(
                                   entr => new { VendorId = entr.VendorID, MFlag = entr.EntranceMFlag, Desc = entr.Description, LineNo = entr.LineNo, Day = entr.Day, Curr = entr.Currency, Cost = entr.Cost, CostGr = entr.CostG }).ToList();

                    var QuotaRest = _context.tblQuotaLunDins.Where(lunch => lunch.QuotaID == CurrentGroup.QuotaID).OrderBy(lunch => lunch.LineNo).Select(
                                   lunch => new { VendorId = lunch.VendorID, MLunchFlag = lunch.LunchMFlag, MDinnerFlag = lunch.DinnerMFlag, Desc = lunch.Description, DescD = lunch.DescriptionD, DVendorID = lunch.DVendorID, LineNo = lunch.LineNo, Day = lunch.Day, Curr = lunch.Currency, Cost = lunch.Cost, CostD = lunch.CostD }).ToList();
                    // Hotel, Rooming 
                    var QuotaHotel = _context.tblQuotaHotels.Where(htl => htl.QuotaID == CurrentGroup.QuotaID).OrderBy(htl => htl.LineNo).Select(
                                   htl => new { VendorId = htl.VendorID, MFlag = htl.HotelMFlag, Desc = htl.Description, LineNo = htl.LineNo, Nights = htl.Nights, Tax = htl.Tax, Curr = htl.Currency, Occtax = htl.OccTax, Luggage = htl.Luggage, Brk = htl.Breakfast, Room = htl.Room }).ToList();
                    // Misc
                    var QuotaMisc = _context.tblQuotaEntrGrps.Where(Entgrp => Entgrp.QuotaID == CurrentGroup.QuotaID).OrderBy(Entgrp => Entgrp.LineNo).Select(
                        Entgrp => new { VendorId = Entgrp.VendorID, Desc = Entgrp.Description, Day = Entgrp.Day, Cost = Entgrp.Cost, CostG = Entgrp.CostG, Curr = Entgrp.Currency, Plane = Entgrp.PlaneFlag }).ToList();
                    
                    int Ind = 0;

                    DateTime ArrDate;
                    DateTime StartDateBus;
                    DateTime StartDateAttr;
                    DateTime StartDateMisc;
                    DateTime StartDateRest;
                    DateTime StartDateGuide;
                    var Services = _context.tblServices.ToList();
                    var Details = _context.tblDetails.ToList();
                    // check old vendor confirms
                    // ---vendor confirm arrival date equal Group arrival date 

                    if (CurrentGroup.ArrivalDate != null)
                    {
                        ArrDate = Convert.ToDateTime(CurrentGroup.ArrivalDate);
                        StartDateBus = Convert.ToDateTime(CurrentGroup.ArrivalDate);
                        StartDateAttr = Convert.ToDateTime(CurrentGroup.ArrivalDate);
                        StartDateMisc = Convert.ToDateTime(CurrentGroup.ArrivalDate);
                        StartDateRest = Convert.ToDateTime(CurrentGroup.ArrivalDate);
                        StartDateGuide = Convert.ToDateTime(CurrentGroup.ArrivalDate);
                        CurrentGroup.IsPushed = true;
                        CurrentGroup.PushedDate = DateTime.Now;
                        CurrentGroup.PushDoneDate = DateTime.Now;
                        // Attr, Rest
                        int IsFindAttrID = 1;
                        if (_context.tblVendors.Any(vnd => vnd.IsFind == true && vnd.VendorTypeID == 4))
                            IsFindAttrID = _context.tblVendors.Where(vnd => vnd.IsFind == true && vnd.VendorTypeID == 4).FirstOrDefault().VendorID;
                        foreach (var attr in QuotaAttr)
                        {
                            StartDateAttr = StartDateAttr.AddDays(Convert.ToInt32(attr.Day) - 1);
                            tblAttractionsConfirm AttrConf = new tblAttractionsConfirm()
                            {
                                GroupID = CurrentGroup.GroupID,
                                VendorID = attr.MFlag == true ? IsFindAttrID : attr.VendorId == null ? IsFindAttrID : Convert.ToInt32(attr.VendorId),
                                QuotaVendorID = attr.MFlag == true ? IsFindAttrID : attr.VendorId == null ? IsFindAttrID : Convert.ToInt32(attr.VendorId),
                                QuotaVendorDesc = attr.Desc,
                                StartDate = StartDateAttr,
                                QuotaCurr = attr.Curr == null ? "USD" : attr.Curr == "US" ? "USD" : "CAD",
                                QuotaRate = attr.Cost,
                                StatusID = 1,
                                UnitRate = attr.Cost,
                                EmployeeID = CurrentGroup.AttrEmpID,
                            };
                            _context.tblAttractionsConfirms.Add(AttrConf);
                        }

                        int IsFindRestID = 1;
                        if (_context.tblVendors.Any(vnd => vnd.IsFind == true && vnd.VendorTypeID == 2))
                            IsFindRestID = _context.tblVendors.Where(vnd => vnd.IsFind == true && vnd.VendorTypeID == 2).FirstOrDefault().VendorID;
                        
                        foreach (var rest in QuotaRest)
                        {
                            StartDateRest = StartDateRest.AddDays(Convert.ToInt32(rest.Day) - 1);
                            tblRestaurantsConfirm RestConf = null;
                            tblRestaurantsConfirm RestConfD = null;
                            if(rest.Cost > 0)
                                RestConf = new tblRestaurantsConfirm()
                                {
                                    GroupID = CurrentGroup.GroupID,
                                    VendorID = rest.MLunchFlag == true ? IsFindRestID : rest.VendorId == null ? IsFindRestID : Convert.ToInt32(rest.VendorId),
                                    QuotaVendorID = rest.MLunchFlag == true ? IsFindRestID : rest.VendorId == null ? IsFindRestID : Convert.ToInt32(rest.VendorId),
                                    QuotaVendorDesc = rest.Desc,
                                    StartDate = StartDateRest,
                                    QuotaCurr = rest.Curr == null ? "USD" : rest.Curr == "US" ? "USD" : "CAD",
                                    QuotaRate = rest.Cost,
                                    Category = "L",
                                    StatusID = 1,
                                    UnitRate = rest.Cost,
                                    EmployeeID = CurrentGroup.AttrEmpID,
                                };
                            if(rest.CostD > 0)
                                RestConfD = new tblRestaurantsConfirm()
                                {
                                    GroupID = CurrentGroup.GroupID,
                                    VendorID = rest.MDinnerFlag == true ? IsFindRestID : rest.DVendorID == null ? IsFindRestID : Convert.ToInt32(rest.DVendorID),
                                    QuotaVendorID = rest.MDinnerFlag == true ? IsFindRestID : rest.DVendorID == null ? IsFindRestID : Convert.ToInt32(rest.DVendorID),
                                    QuotaVendorDesc = rest.DescD,
                                    StartDate = StartDateRest,
                                    QuotaCurr = rest.Curr == null ? "USD" : rest.Curr == "US" ? "USD" : "CAD",
                                    QuotaRate = rest.CostD,
                                    Category = "D",
                                    StatusID = 1,
                                    UnitRate = rest.CostD,
                                    EmployeeID = CurrentGroup.AttrEmpID,
                                };
                            if (RestConf != null)
                                _context.tblRestaurantsConfirms.Add(RestConf);
                            if (RestConfD != null)
                                _context.tblRestaurantsConfirms.Add(RestConfD);
                        }

                        // Misc
                        int IsFindMiscID = 1;
                        if (_context.tblVendors.Any(vnd => vnd.IsFind == true && (vnd.VendorTypeID == 5 || vnd.VendorTypeID == 6 || vnd.VendorTypeID == 7 || vnd.VendorTypeID == 9)))
                            IsFindMiscID = _context.tblVendors.Where(vnd => vnd.IsFind == true && (vnd.VendorTypeID == 5 || vnd.VendorTypeID == 6 || vnd.VendorTypeID == 7 || vnd.VendorTypeID == 9)).FirstOrDefault().VendorID;
                        foreach (var misc in QuotaMisc)
                        {
                            StartDateMisc = StartDateMisc.AddDays(Convert.ToInt32(misc.Day) - 1);
                            tblMiscConfirm MiscConf = new tblMiscConfirm()
                            {
                                GroupID = CurrentGroup.GroupID,
                                VendorID = misc.VendorId == null ? IsFindMiscID : Convert.ToInt32(misc.VendorId),
                                QuotaVendorID = misc.VendorId == null ? IsFindMiscID : Convert.ToInt32(misc.VendorId),
                                QuotaVendorDesc = misc.Desc,
                                StartDate = StartDateMisc,
                                QuotaCurr = misc.Curr == null ? "USD" : misc.Curr == "US" ? "USD" : "CAD",
                                QuotaRate = misc.Cost,
                                QuotaPerGroupRate = misc.CostG,
                                UnitPrice = misc.Cost,
                                UnitPricePerGroup = misc.CostG,
                                StatusID = 1,
                                PlaneFlag = misc.Plane,
                                EmployeeID = CurrentGroup.MiscEmpID,
                            };
                            _context.tblMiscConfirms.Add(MiscConf);
                        }

                        // bus, guide
                        int IsFindBusID = 1;
                        if (_context.tblVendors.Any(vnd => vnd.IsFind == true && vnd.VendorTypeID == 3))
                            IsFindBusID = _context.tblVendors.Where(vnd => vnd.IsFind == true && vnd.VendorTypeID == 3).FirstOrDefault().VendorID;
                        foreach (var bus in QuotaBus)
                        {
                            StartDateBus = StartDateBus.AddDays(Convert.ToInt32(bus.Day) - 1);
                            var ServiceTypeID = Services.Where(serv => serv.ServiceID == bus.ServiceId).Select(serv=>serv.ServiceTypeID).SingleOrDefault();
                            int Dayqty = Convert.ToInt32(bus.DayQty);
                            if (Dayqty > 0)
                                for (int day = 1; day <= Dayqty; day++)
                                {
                                    tblBusConfirm BusConf = new tblBusConfirm()
                                    {
                                        GroupID = CurrentGroup.GroupID,
                                        VendorID = bus.Mflag == true ? IsFindBusID : bus.VendorId == null ? IsFindBusID : Convert.ToInt32(bus.VendorId),
                                        QuotaVendorID = bus.Mflag == true ? IsFindBusID : bus.VendorId == null ? IsFindBusID : Convert.ToInt32(bus.VendorId),
                                        QuotaVendorDesc = bus.Desc,
                                        StartDate = StartDateBus.AddDays(day - 1),
                                        QuotaRate = bus.Rate,
                                        QuotaCurr = bus.Curr == null ? "USD" : bus.Curr == "US" ? "USD" : "CAD",
                                        StatusID = 1,
                                        ServiceID = bus.ServiceId,
                                        ServiceDetID = Details.Any(det => det.ServiceTypeID == ServiceTypeID) ? Details.Where(det => det.ServiceTypeID == ServiceTypeID).FirstOrDefault().DetailID : (int?)null,
                                        BusPrice = bus.Rate,
                                        BusCount = 1,
                                        EmployeeID = CurrentGroup.BusEmpID,
                                    };
                                    _context.tblBusConfirms.Add(BusConf);
                                }
                        }
                        int IsFindGuideID = 1;
                        var GuideMiscSerID = _context.tblServices.Any(ser => ser.ServiceCode.Contains("Guide Misc")) ? _context.tblServices.Where(ser => ser.ServiceCode.Contains("Guide Misc")).FirstOrDefault().ServiceID : 0;
                        var GuideStepSerID = _context.tblServices.Any(ser => ser.ServiceCode.Contains("Step Guide")) ? _context.tblServices.Where(ser => ser.ServiceCode.Contains("Step Guide")).FirstOrDefault().ServiceID : 0;
                        var GuideSerID = _context.tblServices.Any(ser => ser.ServiceCode.Contains("Guide Service")) ? _context.tblServices.Where(ser => ser.ServiceCode.Contains("Guide Service")).FirstOrDefault().ServiceID : 0;
                        var QuotaGuideMisc = QuotaGuide.Where(guide => guide.ServiceId == GuideMiscSerID).ToList();
                        var QuotaGuideStep = QuotaGuide.Where(guide => guide.ServiceId == GuideStepSerID).ToList();
                        if (_context.tblGuides.Any(guide => guide.IsFind == true))
                            IsFindGuideID = _context.tblGuides.Where(guide => guide.IsFind == true).FirstOrDefault().GuideID;
                        foreach (var guide in QuotaGuide)
                        {
                            if (guide.ServiceId != GuideSerID)
                                continue;
                            StartDateGuide = StartDateGuide.AddDays(Convert.ToInt32(guide.Day) - 1);
                            int Dayqty = Convert.ToInt32(guide.DayQty);
                            if (Dayqty > 0)
                                for (int day = 1; day <= Dayqty; day++)
                                {
                                    tblGuideConfirm GuideConf = new tblGuideConfirm()
                                    {
                                        GroupID = CurrentGroup.GroupID,
                                        GuideID = guide.MFlag == true ? IsFindGuideID : guide.GuideId == null ? IsFindGuideID : Convert.ToInt32(guide.GuideId),
                                        QuotaGuideID = guide.MFlag == true ? IsFindGuideID : guide.GuideId == null ? IsFindGuideID : Convert.ToInt32(guide.GuideId),
                                        QuotaGuideDesc = guide.Desc,
                                        StartDate = StartDateGuide.AddDays(day - 1),
                                        QuotaRate = guide.Rate,
                                        QuotaCurr = guide.Curr == null ? "USD" : guide.Curr == "US" ? "USD" : "CAD",
                                        Salary = guide.Rate,
                                        EmployeeID = CurrentGroup.BusEmpID,
                                       // StatusID = 1,
                                    };
                                    foreach (var misc in QuotaGuideMisc)
                                    {
                                        var MiscStartDate = Convert.ToDateTime(CurrentGroup.ArrivalDate).AddDays(Convert.ToInt32(misc.Day) - 1);
                                        var MiscEndDate = Convert.ToDateTime(CurrentGroup.ArrivalDate).AddDays(Convert.ToInt32(misc.Day + misc.DayQty) - 1);
                                        if (GuideConf.StartDate == MiscStartDate || GuideConf.StartDate == MiscEndDate || (GuideConf.StartDate > MiscStartDate && GuideConf.StartDate < MiscEndDate))
                                        {
                                            GuideConf.QuotaGuideMisc = misc.Rate;
                                            GuideConf.GuideMisc = misc.Rate;
                                        }
                                    }
                                    foreach (var step in QuotaGuideStep)
                                    {
                                        var StepStartDate = Convert.ToDateTime(CurrentGroup.ArrivalDate).AddDays(Convert.ToInt32(step.Day) - 1);
                                        var StepEndDate = Convert.ToDateTime(CurrentGroup.ArrivalDate).AddDays(Convert.ToInt32(step.Day + step.DayQty) - 1);
                                        if (GuideConf.StartDate == StepStartDate || GuideConf.StartDate == StepEndDate || (GuideConf.StartDate > StepStartDate && GuideConf.StartDate < StepEndDate))
                                        {
                                            GuideConf.QuotaGuideStepOn = step.Rate;
                                            GuideConf.GuideStepOn = step.Rate;
                                        }
                                    }
                                        
                                    _context.tblGuideConfirms.Add(GuideConf);
                                }
                        }
                        // hotel, room
                        int IsFindHotelID = 1;
                        if (_context.tblVendors.Any(vnd => vnd.IsFind == true && vnd.VendorTypeID == 1))
                            IsFindHotelID = _context.tblVendors.Where(vnd => vnd.IsFind == true && vnd.VendorTypeID == 1).FirstOrDefault().VendorID;

                        var ABFSerID = _context.tblServices.Where(ser => ser.ServiceCode == "American" && ser.Active == true).Select(ser => ser.ServiceID).FirstOrDefault();
                        var CBFSerID = _context.tblServices.Where(ser => ser.ServiceCode == "Continental" && ser.Active == true).Select(ser => ser.ServiceID).FirstOrDefault();
                        var LunchSerID = _context.tblServices.Where(ser => ser.ServiceTypeID == 3 && ser.ServiceCode == "Lunch" && ser.Active == true).Select(ser => ser.ServiceID).FirstOrDefault();
                        var DinnerSerID = _context.tblServices.Where(ser => ser.ServiceTypeID == 4 && ser.ServiceCode == "Dinner" && ser.Active == true).Select(ser => ser.ServiceID).FirstOrDefault();
                        var BaggageID = _context.tblServices.Where(ser => ser.ServiceTypeID == 1 && ser.ServiceCode == "Baggage" && ser.Active == true).Select(ser => ser.ServiceID).FirstOrDefault();
                        var VendIds = QuotaHotel.Select(htl=>htl.VendorId).ToList();
                        var VendorInfo = _context.tblVendors.Where(vnd => VendIds.Contains(vnd.VendorID)).Select(vnd => new { VendorId = vnd.VendorID, VendorCity = vnd.City }).ToList();
                        foreach (var htl in QuotaHotel)
                        {
                            ArrDate = Ind == 0 ? ArrDate : QuotaHotel[Ind - 1].Nights != null ? ArrDate.AddDays(Convert.ToInt32(QuotaHotel[Ind - 1].Nights)) : ArrDate;
                            // pushing to operation
                            string city = VendorInfo.Any(vnd => vnd.VendorId == htl.VendorId) ? VendorInfo.Where(vnd => vnd.VendorId == htl.VendorId).Select(vnd => vnd.VendorCity).FirstOrDefault() : "";
                            tblVendorConfirm NewVConf = new tblVendorConfirm()
                            {
                                GroupID = CurrentGroup.GroupID,
                                ArrivalDate = ArrDate,
                                DepartureDate = htl.Nights != null ? ArrDate.AddDays(Convert.ToInt32(htl.Nights)) : ArrDate,
                                VendorID = htl.MFlag == true ? IsFindHotelID : htl.VendorId == null ? IsFindHotelID : Convert.ToInt32(htl.VendorId),
                                QuotaVendorID = htl.MFlag == true ? IsFindHotelID : htl.VendorId == null ? IsFindHotelID : Convert.ToInt32(htl.VendorId),
                                QuotaVendorDesc = htl.Desc,
                                QuotaRoom = htl.Room,
                                QuotaBrk = htl.Brk,
                                QuotaLg = htl.Luggage,
                                QuotaNight = htl.Nights,
                                QuotaTax = htl.Tax,
                                Rooms = CurrentGroup.Rooms,
                                Pax = CurrentGroup.NoOfPerson,
                                Tax = htl.Tax,
                                OccTax = htl.Occtax,
                                Luggage = htl.Luggage,
                                StatusID = 1,
                                QuotaCurr = htl.Curr == null ? "USD" : htl.Curr == "US" ? "USD" : "CAD",
                                CommissionStatus = "Pending",
                                ArrivalCity = city,
                                EmployeeID = CurrentGroup.HotelEmpID,
                            };
                            _context.tblVendorConfirms.Add(NewVConf);
                            Ind++;
                            // Double rooms added as default
                            if (htl.Nights != null && htl.VendorId != null && htl.Nights > 0)
                            { 
                                var LineNo = 1;
                                for (int ind = 1; ind <= htl.Nights; ind++)
                                {
                                    // Double equal est rooms count
                                    tblVConfirmDetail VDetailDouble = new tblVConfirmDetail()
                                    {
                                        GroupID = CurrentGroup.GroupID,
                                        VendorID = htl.MFlag == true ? IsFindHotelID : htl.VendorId == null ? IsFindHotelID : Convert.ToInt32(htl.VendorId),
                                        ServiceID = 2,
                                        XQty = 1,
                                        Qty = CurrentGroup.Rooms,
                                        Price = htl.Room,
                                        StartDate = ArrDate.AddDays(ind - 1),
                                        EndDate = ArrDate.AddDays(ind),
                                        LineNo = LineNo,
                                        ArrivalDate = ArrDate,
                                    };
                                    _context.tblVConfirmDetails.Add(VDetailDouble);
                                    LineNo++;
                                    // Single equal 0
                                    tblVConfirmDetail VDetailSingle = new tblVConfirmDetail()
                                    {
                                        GroupID = CurrentGroup.GroupID,
                                        VendorID = htl.MFlag == true ? IsFindHotelID : htl.VendorId == null ? IsFindHotelID : Convert.ToInt32(htl.VendorId),
                                        ServiceID = 1,
                                        XQty = 1,
                                        Qty = 0,
                                        Price = 0,
                                        StartDate = ArrDate.AddDays(ind - 1),
                                        EndDate = ArrDate.AddDays(ind),
                                        LineNo = LineNo,
                                        ArrivalDate = ArrDate,
                                    };
                                    _context.tblVConfirmDetails.Add(VDetailSingle);
                                    LineNo++;
                                    // Twin equal 0
                                    tblVConfirmDetail VDetailTwin = new tblVConfirmDetail()
                                    {
                                        GroupID = CurrentGroup.GroupID,
                                        VendorID = htl.MFlag == true ? IsFindHotelID : htl.VendorId == null ? IsFindHotelID : Convert.ToInt32(htl.VendorId),
                                        ServiceID = 3,
                                        XQty = 1,
                                        Qty = 0,
                                        Price = 0,
                                        StartDate = ArrDate.AddDays(ind - 1),
                                        EndDate = ArrDate.AddDays(ind),
                                        LineNo = LineNo,
                                        ArrivalDate = ArrDate,
                                    };
                                    _context.tblVConfirmDetails.Add(VDetailTwin);
                                    LineNo++;
                                    // Triple equal 0
                                    tblVConfirmDetail VDetailTriple = new tblVConfirmDetail()
                                    {
                                        GroupID = CurrentGroup.GroupID,
                                        VendorID = htl.MFlag == true ? IsFindHotelID : htl.VendorId == null ? IsFindHotelID : Convert.ToInt32(htl.VendorId),
                                        ServiceID = 4,
                                        XQty = 1,
                                        Qty = 0,
                                        Price = 0,
                                        StartDate = ArrDate.AddDays(ind - 1),
                                        EndDate = ArrDate.AddDays(ind),
                                        LineNo = LineNo,
                                        ArrivalDate = ArrDate,
                                    };
                                    _context.tblVConfirmDetails.Add(VDetailTriple);
                                    LineNo++;
                                    // Quad equal 0
                                    tblVConfirmDetail VDetailQuad = new tblVConfirmDetail()
                                    {
                                        GroupID = CurrentGroup.GroupID,
                                        VendorID = htl.MFlag == true ? IsFindHotelID : htl.VendorId == null ? IsFindHotelID : Convert.ToInt32(htl.VendorId),
                                        ServiceID = 5,
                                        XQty = 1,
                                        Qty = 0,
                                        Price = 0,
                                        StartDate = ArrDate.AddDays(ind - 1),
                                        EndDate = ArrDate.AddDays(ind),
                                        LineNo = LineNo,
                                        ArrivalDate = ArrDate,
                                    };
                                    _context.tblVConfirmDetails.Add(VDetailQuad);
                                    LineNo++;
                                    // Guide equal 0
                                    tblVConfirmDetail VDetailGuide = new tblVConfirmDetail()
                                    {
                                        GroupID = CurrentGroup.GroupID,
                                        VendorID = htl.MFlag == true ? IsFindHotelID : htl.VendorId == null ? IsFindHotelID : Convert.ToInt32(htl.VendorId),
                                        ServiceID = 87,
                                        XQty = 1,
                                        Qty = 0,
                                        Price = 0,
                                        StartDate = ArrDate.AddDays(ind - 1),
                                        EndDate = ArrDate.AddDays(ind),
                                        LineNo = LineNo,
                                        ArrivalDate = ArrDate,
                                    };
                                    _context.tblVConfirmDetails.Add(VDetailGuide);
                                    LineNo++;
                                    // Driver equal 0
                                    tblVConfirmDetail VDetailDriver = new tblVConfirmDetail()
                                    {
                                        GroupID = CurrentGroup.GroupID,
                                        VendorID = htl.MFlag == true ? IsFindHotelID : htl.VendorId == null ? IsFindHotelID : Convert.ToInt32(htl.VendorId),
                                        ServiceID = 79,
                                        XQty = 1,
                                        Qty = 0,
                                        Price = 0,
                                        StartDate = ArrDate.AddDays(ind - 1),
                                        EndDate = ArrDate.AddDays(ind),
                                        LineNo = LineNo,
                                        ArrivalDate = ArrDate,
                                    };
                                    _context.tblVConfirmDetails.Add(VDetailDriver);
                                    LineNo++;
                                    if (ABFSerID != 0)
                                    {
                                        tblVConfirmDetail VDetailABF = new tblVConfirmDetail()
                                        {
                                            GroupID = CurrentGroup.GroupID,
                                            VendorID = htl.MFlag == true ? IsFindHotelID : htl.VendorId == null ? IsFindHotelID : Convert.ToInt32(htl.VendorId),
                                            ServiceID = ABFSerID,
                                            XQty = 1,
                                            Qty = CurrentGroup.NoOfPerson,
                                            Price = 0,
                                            StartDate = ArrDate.AddDays(ind - 1),
                                            EndDate = ArrDate.AddDays(ind),
                                            LineNo = LineNo,
                                            ArrivalDate = ArrDate,
                                        };
                                        _context.tblVConfirmDetails.Add(VDetailABF);
                                        LineNo++;
                                        if(ind == htl.Nights)
                                        {
                                         // more 1 day for meal
                                            tblVConfirmDetail VDetailABFLast = new tblVConfirmDetail()
                                            {
                                                GroupID = CurrentGroup.GroupID,
                                                VendorID = htl.MFlag == true ? IsFindHotelID : htl.VendorId == null ? IsFindHotelID : Convert.ToInt32(htl.VendorId),
                                                ServiceID = ABFSerID,
                                                XQty = 1,
                                                Qty = CurrentGroup.NoOfPerson,
                                                Price = 0,
                                                StartDate = ArrDate.AddDays(ind),
                                                EndDate = ArrDate.AddDays(ind+1),
                                                LineNo = LineNo,
                                                ArrivalDate = ArrDate,
                                            };
                                            _context.tblVConfirmDetails.Add(VDetailABFLast);
                                            LineNo++;
                                        }
                                    }
                                    if (CBFSerID != 0)
                                    {
                                        tblVConfirmDetail VDetailCBF = new tblVConfirmDetail()
                                        {
                                            GroupID = CurrentGroup.GroupID,
                                            VendorID = htl.MFlag == true ? IsFindHotelID : htl.VendorId == null ? IsFindHotelID : Convert.ToInt32(htl.VendorId),
                                            ServiceID = CBFSerID,
                                            XQty = 1,
                                            Qty = CurrentGroup.NoOfPerson,
                                            Price = 0,
                                            StartDate = ArrDate.AddDays(ind - 1),
                                            EndDate = ArrDate.AddDays(ind),
                                            LineNo = LineNo,
                                            ArrivalDate = ArrDate,
                                        };
                                        _context.tblVConfirmDetails.Add(VDetailCBF);
                                        LineNo++;
                                        if (ind == htl.Nights)
                                        {
                                            // more 1 day for meal
                                            tblVConfirmDetail VDetailCBFLast = new tblVConfirmDetail()
                                            {
                                                GroupID = CurrentGroup.GroupID,
                                                VendorID = htl.MFlag == true ? IsFindHotelID : htl.VendorId == null ? IsFindHotelID : Convert.ToInt32(htl.VendorId),
                                                ServiceID = CBFSerID,
                                                XQty = 1,
                                                Qty = CurrentGroup.NoOfPerson,
                                                Price = 0,
                                                StartDate = ArrDate.AddDays(ind),
                                                EndDate = ArrDate.AddDays(ind + 1),
                                                LineNo = LineNo,
                                                ArrivalDate = ArrDate,
                                            };
                                            _context.tblVConfirmDetails.Add(VDetailCBFLast);
                                            LineNo++;
                                        }
                                    }
                                    if (LunchSerID != 0)
                                    {
                                        tblVConfirmDetail VDetailLunch = new tblVConfirmDetail()
                                        {
                                            GroupID = CurrentGroup.GroupID,
                                            VendorID = htl.MFlag == true ? IsFindHotelID : htl.VendorId == null ? IsFindHotelID : Convert.ToInt32(htl.VendorId),
                                            ServiceID = LunchSerID,
                                            XQty = 1,
                                            Qty = CurrentGroup.NoOfPerson,
                                            Price = 0,
                                            StartDate = ArrDate.AddDays(ind - 1),
                                            EndDate = ArrDate.AddDays(ind),
                                            LineNo = LineNo,
                                            ArrivalDate = ArrDate,
                                        };
                                        _context.tblVConfirmDetails.Add(VDetailLunch);
                                        LineNo++;
                                        if (ind == htl.Nights)
                                        {
                                            // more 1 day for meal
                                            tblVConfirmDetail VDetailLunchLast = new tblVConfirmDetail()
                                            {
                                                GroupID = CurrentGroup.GroupID,
                                                VendorID = htl.MFlag == true ? IsFindHotelID : htl.VendorId == null ? IsFindHotelID : Convert.ToInt32(htl.VendorId),
                                                ServiceID = LunchSerID,
                                                XQty = 1,
                                                Qty = CurrentGroup.NoOfPerson,
                                                Price = 0,
                                                StartDate = ArrDate.AddDays(ind),
                                                EndDate = ArrDate.AddDays(ind + 1),
                                                LineNo = LineNo,
                                                ArrivalDate = ArrDate,
                                            };
                                            _context.tblVConfirmDetails.Add(VDetailLunchLast);
                                            LineNo++;
                                        }
                                    }
                                    if (DinnerSerID != 0)
                                    {
                                        tblVConfirmDetail VDetailDinner = new tblVConfirmDetail()
                                        {
                                            GroupID = CurrentGroup.GroupID,
                                            VendorID = htl.MFlag == true ? IsFindHotelID : htl.VendorId == null ? IsFindHotelID : Convert.ToInt32(htl.VendorId),
                                            ServiceID = DinnerSerID,
                                            XQty = 1,
                                            Qty = CurrentGroup.NoOfPerson,
                                            Price = 0,
                                            StartDate = ArrDate.AddDays(ind - 1),
                                            EndDate = ArrDate.AddDays(ind),
                                            LineNo = LineNo,
                                            ArrivalDate = ArrDate,
                                        };
                                        _context.tblVConfirmDetails.Add(VDetailDinner);
                                        LineNo++;
                                        if (ind == htl.Nights)
                                        {
                                            // more 1 day for meal
                                            tblVConfirmDetail VDetailDinnerLast = new tblVConfirmDetail()
                                            {
                                                GroupID = CurrentGroup.GroupID,
                                                VendorID = htl.MFlag == true ? IsFindHotelID : htl.VendorId == null ? IsFindHotelID : Convert.ToInt32(htl.VendorId),
                                                ServiceID = DinnerSerID,
                                                XQty = 1,
                                                Qty = CurrentGroup.NoOfPerson,
                                                Price = 0,
                                                StartDate = ArrDate.AddDays(ind),
                                                EndDate = ArrDate.AddDays(ind + 1),
                                                LineNo = LineNo,
                                                ArrivalDate = ArrDate,
                                            };
                                            _context.tblVConfirmDetails.Add(VDetailDinnerLast);
                                            LineNo++;
                                        }
                                    }
                                    if (BaggageID != 0)
                                    {
                                        tblVConfirmDetail VDetailBaggage = new tblVConfirmDetail()
                                        {
                                            GroupID = CurrentGroup.GroupID,
                                            VendorID = htl.MFlag == true ? IsFindHotelID : htl.VendorId == null ? IsFindHotelID : Convert.ToInt32(htl.VendorId),
                                            ServiceID = BaggageID,
                                            XQty = 1,
                                            Qty = CurrentGroup.NoOfPerson,
                                            Price = 0,
                                            StartDate = ArrDate.AddDays(ind - 1),
                                            EndDate = ArrDate.AddDays(ind),
                                            LineNo = LineNo,
                                            ArrivalDate = ArrDate,
                                        };
                                        _context.tblVConfirmDetails.Add(VDetailBaggage);
                                        LineNo++;
                                    }
                                }
                            }     
                        }
                        // Invoice for ad hoc
                        if (CurrentGroup.IsGir == false)
                        {
                            int LineNo = 1;
                            CurrentGroup.FinalPayment = Convert.ToDateTime(CurrentGroup.ArrivalDate).AddDays(-30);
                            CurrentGroup.BankID = 1;
                            CurrentGroup.PayTerms = "Swift";
                            decimal price = Math.Round(Convert.ToDecimal(OperationRepository.PerPersonPriceByPax((int)CurrentGroup.NoOfPerson, "USD", CurrentGroup.QuotaID)));
                            //per Pers USD
                            if (price > 0)
                            {
                                tblGroupInvoice InvoicePerP = new tblGroupInvoice()
                                {
                                    GroupID = CurrentGroup.GroupID,
                                    LineNo = LineNo,
                                    ItemTypeID = 0,
                                    Price = price,
                                    PriceChange = price,
                                    Quantity = (int)CurrentGroup.NoOfPerson,
                                    Description = "Per Person",
                                    Currency = "USD",
                                    DebCre = false,
                                    Debit = (int)CurrentGroup.NoOfPerson * price,
                                };
                                _context.tblGroupInvoices.Add(InvoicePerP);
                                LineNo++;
                            }
                            price = Math.Round(Convert.ToDecimal(OperationRepository.PerPersonPriceByPax((int)CurrentGroup.Rooms, "CAD", CurrentGroup.QuotaID)));
                            //per Pers CAD
                            if (price > 0)
                            {
                                tblGroupInvoice InvoicePerC = new tblGroupInvoice()
                                {
                                    GroupID = CurrentGroup.GroupID,
                                    LineNo = LineNo,
                                    ItemTypeID = 0,
                                    Price = price,
                                    PriceChange = price,
                                    Quantity = (int)CurrentGroup.NoOfPerson,
                                    Description = "Per Person",
                                    Currency = "CAD",
                                    DebCre = false,
                                    Debit = (int)CurrentGroup.NoOfPerson * price,
                                };
                                _context.tblGroupInvoices.Add(InvoicePerC);
                            }
                        }
                        // save all
                        try
                        {
                            _context.SaveChanges();
                            transaction.Commit();
                        }
                        catch (Exception ex)
                        {
                            Error.Message = GetExceptionMessages(ex);
                            Error.errorCode = 1010;
                            logger.Error(ex.ToMessageAndCompleteStacktrace());
                            transaction.Rollback();
                        }
                    }
                }
                return Error;
            }
        }
        private QuotationGirGroupGridBData InitQuotaGirGroupGridBData(IQueryable<tblGroup> Group, bool Gir, string UserName)
        {
            QuotationGirGroupGridBData GrGirData = new QuotationGirGroupGridBData();
            var BonjourUsaCorp = _context.tblCustomers.Where(cust => cust.OrganizationName == "Bonjour USA").SingleOrDefault();
            if (BonjourUsaCorp != null)
            {
                GrGirData.Customer = BonjourUsaCorp.OrganizationName;
                GrGirData.SelCustomerID = BonjourUsaCorp.CustomerID;
            }
            GrGirData.GroupID = 1;
            if (_context.tblGroups.Count() > 0)
                GrGirData.GroupID = _context.tblGroups.Max(gr => gr.GroupID) + 1;
            GrGirData.Rows = new List<QuotationGirGroupGridRowBData>();
            GrGirData.Rows = (from Gr in Group
                              from cus in _context.tblCustomers
                              where Gr.CustomerID == cus.CustomerID && Gr.IsGir == Gir
                              select new QuotationGirGroupGridRowBData()
                              {
                                  IsPushed = Gr.IsPushed,
                                  GroupID = Gr.GroupID,
                                  GroupCode = Gr.GroupCode,
                                  GroupName = Gr.GroupName,
                                  SelCustomerID = Gr.CustomerID,
                                  SeasonID = Gr.SeasonID,
                                  ArrivalDate = Gr.ArrivalDate,
                                  DepartureDate = Gr.DepartureDate,
                                  Customer = cus.OrganizationName,
                                  SelStatusID = Gr.StatusID,
                                  SelCountryID = Gr.CountryID,
                                  AsGir = Gr.AsGir,
                                  Room = Gr.Rooms,
                                  Pax = Gr.NoOfPerson,
                                  CancelInfo = Gr.CanceledInfo,
                              }).ToList();
            foreach (var row in GrGirData.Rows)
                if (row.DepartureDate != null && row.ArrivalDate != null)
                    row.NoNights = (short)(Convert.ToDateTime(row.DepartureDate) - Convert.ToDateTime(row.ArrivalDate)).Days;
            GrGirData.Status = (from GrSt in _context.tblGroupStatus
                                select new
                                {
                                    Key = GrSt.StatusID,
                                    Value = GrSt.StatusDesc
                                }).ToDictionary(o => o.Key, o => o.Value);

            GrGirData.Country = (from Ctr in _context.tblCountries
                                 where Ctr.Active == true
                                 orderby Ctr.OrderNum, Ctr.CountryName
                                 select new
                                {
                                    Key = Ctr.CountryID,
                                    Value = Ctr.CountryName
                                }).ToDictionary(o => o.Key, o => o.Value);
            // Employee
            var EmpID = _context.Users.Where(usr => usr.UserName == UserName).Select(usr => usr.EmployeeID).SingleOrDefault();
            var SupEmp = _context.tblSupEmployees.Where(sup => sup.EmployeeID == EmpID).Select(sup => sup.SupervisorID).ToList();
            GrGirData.Employee = (from user in _context.Users
                                  from emp in _context.tblEmployees
                                  where user.EmployeeID == emp.EmployeeID && emp.Active == true && SupEmp.Contains((int)user.EmployeeID)
                                  orderby emp.FirstName, emp.LastName
                                  select new
                                  {
                                      Key = emp.EmployeeID,
                                      Value = emp.FirstName + " " + emp.LastName,
                                  }).ToDictionary(o => o.Key, o => o.Value);
            if(Gir)
            {
                GrGirData.InvGirGroups = (from gr in GrGirData.Rows select new {Key = gr.GroupID, Value = gr.GroupCode,}).ToDictionary(o => o.Key, o => o.Value);
                
                GrGirData.InvRows = new List<QuotationGirInvoiceGridRowBData>();
                GrGirData.InvRows = (from Gr in Group
                                     from cus in _context.tblCustomers
                                     from GirDet in _context.tblGirGroupDetails
                                     where GirDet.GroupID == Gr.GroupID && GirDet.CustomerID == cus.CustomerID && Gr.IsGir == Gir                       
                                     select new QuotationGirInvoiceGridRowBData()
                                     {
                                         InvGroupID = Gr.GroupID,
                                         InvGroupCode = Gr.GroupCode,
                                         InvGroupName = Gr.GroupName,
                                         InvCustomerID = GirDet.CustomerID,
                                         InvArrivalDate = Gr.ArrivalDate,
                                         InvCustomer = cus.OrganizationName,
                                         InvPricePerPerson = GirDet.PricePerPerson,
                                         InvPriceFBoard = GirDet.PriceFullBoard,
                                         InvPriceSingleSup = GirDet.PriceSingleSupp,
                                         InvPricePerPersonCad = GirDet.PricePerPersonCad,
                                         InvPriceFBoardCad = GirDet.PriceFullBoardCad,
                                         InvPriceSingleSupCad = GirDet.PriceSingleSuppCad,
                                     }).ToList();
                var GroupIds = GrGirData.InvRows.Select(row => row.InvGroupID).ToList();
                var CustIds = GrGirData.InvRows.Select(row => row.InvCustomerID).ToList();
                var GroupRooms = _context.tblGroupRooms.Where(room => room.CustomerID != null && GroupIds.Contains(room.GroupID) && CustIds.Contains((int)room.CustomerID)).ToList();
                foreach (var row in GrGirData.InvRows)
                    row.InvPax = GroupRooms.Where(room => room.GroupID == row.InvGroupID && room.CustomerID == row.InvCustomerID).Sum(room => room.NoOfPerson);

            }
            return GrGirData;
        }
        private void CalcDriverRoom(int QuotaId, out decimal DriverRoom, out decimal DriverRoomCad)
        {
            DriverRoom = 0;
            DriverRoomCad = 0;
            decimal? RoomTax = 0;
            try
            {
                var Data = (from quhotel in _context.tblQuotaHotels
                            where quhotel.QuotaID == QuotaId
                            orderby quhotel.LineNo
                            select new
                            {
                                DriverFlag = quhotel.DriverFlag,
                                Nights = quhotel.Nights,
                                OccTax = quhotel.OccTax,
                                Room = quhotel.Room,
                                tax = quhotel.Tax,
                                Currency = quhotel.Currency,
                            }).ToList();
                foreach (var hotel in Data)
                {
                    RoomTax = hotel.Room + (Convert.ToDecimal(hotel.Room) * Convert.ToDecimal(hotel.tax)) / 100 + hotel.OccTax;

                    if (hotel.DriverFlag != null && hotel.DriverFlag == true && hotel.Currency != StringHelper.CurrencyCad)
                        DriverRoom += Convert.ToDecimal(RoomTax * hotel.Nights);
                    if (hotel.DriverFlag != null && hotel.DriverFlag == true && hotel.Currency == StringHelper.CurrencyCad)
                        DriverRoomCad += Convert.ToDecimal(RoomTax * hotel.Nights);
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            
        }
        private void CalcGuideExpenses(int QuotaId, out decimal? accomExpenses, out decimal? accomExpensesCad, out decimal? mealExpenses, out decimal? mealExpensesCad, out decimal? entranceExpenses, out decimal? entranceExpensesCad)
        {
            accomExpenses = 0;
            accomExpensesCad = 0;
            decimal? RoomTax = 0;
            mealExpenses = 0;
            mealExpensesCad = 0;
            entranceExpenses = 0;
            entranceExpensesCad = 0;
            try
            {
                var Data = (from quhotel in _context.tblQuotaHotels
                            where quhotel.QuotaID == QuotaId
                            orderby quhotel.LineNo
                            select new
                            {
                                Nights = quhotel.Nights,
                                OccTax = quhotel.OccTax,
                                Room = quhotel.Room,
                                tax = quhotel.Tax,
                                BGuideFlag = quhotel.BGuideFlag,
                                GuideFlag = quhotel.GuideFlag,
                                Breakfast = quhotel.Breakfast,
                                Luggage = quhotel.Luggage,
                                Currency = quhotel.Currency,
                            }).ToList();
                foreach (var hotel in Data)
                {
                    RoomTax = hotel.Room + (Convert.ToDecimal(hotel.Room) * Convert.ToDecimal(hotel.tax)) / 100 + hotel.OccTax;

                    if (hotel.GuideFlag != null && hotel.GuideFlag == true && hotel.Currency != StringHelper.CurrencyCad)
                        accomExpenses += Convert.ToDecimal(RoomTax * hotel.Nights + hotel.Luggage);
                    if (hotel.GuideFlag != null && hotel.GuideFlag == true && hotel.Currency == StringHelper.CurrencyCad)
                        accomExpensesCad += Convert.ToDecimal(RoomTax * hotel.Nights + hotel.Luggage);
                    if (hotel.BGuideFlag != null && hotel.BGuideFlag == true && hotel.Currency != StringHelper.CurrencyCad)
                        accomExpenses += Convert.ToDecimal(hotel.Breakfast * hotel.Nights);
                    if (hotel.BGuideFlag != null && hotel.BGuideFlag == true && hotel.Currency == StringHelper.CurrencyCad)
                        accomExpensesCad += Convert.ToDecimal(hotel.Breakfast * hotel.Nights);
                }
                var LunchData = (from qulunch in _context.tblQuotaLunDins
                                        where qulunch.QuotaID == QuotaId
                                        orderby qulunch.LineNo
                                        select new
                                        {
                                            Cost = qulunch.Cost,
                                            CostD = qulunch.CostD,  
                                            GuideFlag = qulunch.GuideFlag,
                                            GuideFlagD = qulunch.GuideFlagD,
                                            Currency = qulunch.Currency,
                                        }).ToList();

                foreach (var lunch in LunchData)
                {
                    if (lunch.GuideFlag != null && lunch.GuideFlag == true && lunch.Currency != StringHelper.CurrencyCad)
                        mealExpenses = Convert.ToDecimal(mealExpenses + lunch.Cost);
                    if (lunch.GuideFlag != null && lunch.GuideFlag == true && lunch.Currency == StringHelper.CurrencyCad)
                        mealExpensesCad = Convert.ToDecimal(mealExpensesCad + lunch.Cost);

                    if (lunch.GuideFlagD != null && lunch.GuideFlagD == true && lunch.Currency != StringHelper.CurrencyCad)
                        mealExpenses = Convert.ToDecimal(mealExpenses + lunch.CostD);
                    if (lunch.GuideFlagD != null && lunch.GuideFlagD == true && lunch.Currency == StringHelper.CurrencyCad)
                        mealExpensesCad = Convert.ToDecimal(mealExpensesCad + lunch.CostD);
                }
                var EntrData = (from quentr in _context.tblQuotaEntrances
                                           where quentr.QuotaID == QuotaId
                                           orderby quentr.LineNo
                                           select new 
                                           {
                                               Cost = quentr.Cost,
                                               GuideFlag = quentr.GuideFlag,
                                               Currency = quentr.Currency,
                                           }).ToList();

                foreach (var entrance in EntrData)
                {
                    if (entrance.GuideFlag != null && entrance.GuideFlag == true && entrance.Currency != StringHelper.CurrencyCad)
                        entranceExpenses = Convert.ToDecimal(entranceExpenses + entrance.Cost);
                    if (entrance.GuideFlag != null && entrance.GuideFlag == true && entrance.Currency == StringHelper.CurrencyCad)
                        entranceExpensesCad = Convert.ToDecimal(entranceExpensesCad + entrance.Cost);
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            
        }

        private void InitHotelGridBData(int QuotaId, QuotaHotelGridBData QuotaHotelBData)
        {
            QuotaHotelBData.QuotaHotelConfirmed = _context.tblQuotations.Where(Quota => Quota.QuotaID == QuotaId).Select(Quota => Quota.Accepted).SingleOrDefault();
            QuotaHotelBData.Rows = new List<QuotaHotelGridRowBData>();
            QuotaHotelBData.Rows = (from quhotel in _context.tblQuotaHotels
                                    where quhotel.QuotaID == QuotaId
                                    orderby quhotel.LineNo
                                    select new QuotaHotelGridRowBData()
                                    {
                                        ItmID = quhotel.QuotaHotelID,
                                        VendorID = quhotel.VendorID,
                                        QuotaID = quhotel.QuotaID,
                                        BFreeFlag = quhotel.BFreeFlag,
                                        BGuideFlag = quhotel.BGuideFlag,
                                        Breakfast = quhotel.Breakfast,
                                        BrfType = quhotel.BrfType,
                                        DriverFlag = quhotel.DriverFlag,
                                        FreeFlag = quhotel.FreeFlag,
                                        GuideFlag = quhotel.GuideFlag,
                                        HotelDesc = quhotel.Description,
                                        HotelMFlag = quhotel.HotelMFlag,
                                        LineNo = quhotel.LineNo,
                                        Luggage = quhotel.Luggage,
                                        Nights = quhotel.Nights,
                                        OccTax = quhotel.OccTax,
                                        Room = quhotel.Room,
                                        // RoomPP = Math.Round(((quhotel.Room + (Convert.ToDecimal(quhotel.Room) * Convert.ToDecimal(quhotel.Tax)) / 100 + quhotel.OccTax) / 2)+quhotel.Breakfast),
                                        // RoomTax = quhotel.Room + (Convert.ToDecimal(quhotel.Room) * Convert.ToDecimal(quhotel.Tax)) / 100 + quhotel.OccTax,
                                        // RoomTax2 = (quhotel.Room + (Convert.ToDecimal(quhotel.Room) * Convert.ToDecimal(quhotel.Tax)) / 100 + quhotel.OccTax) / 2,
                                        // SingleSupp = quhotel.singlesup,
                                        tax = quhotel.Tax,
                                        Currency = quhotel.Currency
                                    }).ToList();
            short NightSum = 0;
            foreach (var hotel in QuotaHotelBData.Rows)
            {
                hotel.RoomTax = hotel.Room + (Convert.ToDecimal(hotel.Room) * Convert.ToDecimal(hotel.tax)) / 100 + hotel.OccTax;
                hotel.RoomTax2 = hotel.RoomTax / 2;
                hotel.RoomPP = Convert.ToDecimal(hotel.Nights * (hotel.RoomTax2 + hotel.Breakfast) + hotel.Luggage);
                hotel.SingleSupp = Convert.ToDecimal(hotel.Nights * hotel.RoomTax2);
                if (hotel.GuideFlag != null && hotel.GuideFlag == true && hotel.Currency != StringHelper.CurrencyCad)
                    QuotaHotelBData.GuideSum += Convert.ToDecimal(hotel.RoomTax * hotel.Nights + hotel.Luggage);
                if (hotel.GuideFlag != null && hotel.GuideFlag == true && hotel.Currency == StringHelper.CurrencyCad)
                    QuotaHotelBData.GuideSumCad += Convert.ToDecimal(hotel.RoomTax * hotel.Nights + hotel.Luggage);
                if (hotel.BGuideFlag != null && hotel.BGuideFlag == true && hotel.Currency != StringHelper.CurrencyCad)
                    QuotaHotelBData.GuideSum += Convert.ToDecimal(hotel.Breakfast * hotel.Nights);
                if (hotel.BGuideFlag != null && hotel.BGuideFlag == true && hotel.Currency == StringHelper.CurrencyCad)
                    QuotaHotelBData.GuideSumCad += Convert.ToDecimal(hotel.Breakfast * hotel.Nights);
                if (hotel.FreeFlag != null && hotel.FreeFlag == true && hotel.Currency != StringHelper.CurrencyCad)
                    QuotaHotelBData.FreeSum += Convert.ToDecimal(hotel.RoomTax * hotel.Nights);
                if (hotel.FreeFlag != null && hotel.FreeFlag == true && hotel.Currency == StringHelper.CurrencyCad)
                    QuotaHotelBData.FreeSumCad += Convert.ToDecimal(hotel.RoomTax * hotel.Nights);
                if (hotel.BFreeFlag != null && hotel.BFreeFlag == true && hotel.Currency != StringHelper.CurrencyCad)
                    QuotaHotelBData.FreeSum += Convert.ToDecimal(hotel.Breakfast * hotel.Nights);
                if (hotel.BFreeFlag != null && hotel.BFreeFlag == true && hotel.Currency == StringHelper.CurrencyCad)
                    QuotaHotelBData.FreeSumCad += Convert.ToDecimal(hotel.Breakfast * hotel.Nights);
                if (hotel.DriverFlag != null && hotel.DriverFlag == true && hotel.Currency != StringHelper.CurrencyCad)
                    QuotaHotelBData.DriverSum += Convert.ToDecimal(hotel.RoomTax * hotel.Nights);
                if (hotel.DriverFlag != null && hotel.DriverFlag == true && hotel.Currency == StringHelper.CurrencyCad)
                    QuotaHotelBData.DriverSumCad += Convert.ToDecimal(hotel.RoomTax * hotel.Nights);
                if (hotel.Currency != StringHelper.CurrencyCad)
                    QuotaHotelBData.BreakfSum = Convert.ToDecimal(QuotaHotelBData.BreakfSum + hotel.Breakfast * hotel.Nights);
                if (hotel.Currency == StringHelper.CurrencyCad)
                    QuotaHotelBData.BreakfSumCad = Convert.ToDecimal(QuotaHotelBData.BreakfSumCad + hotel.Breakfast * hotel.Nights);
                NightSum += Convert.ToInt16(hotel.Nights);
                if (hotel.Currency != StringHelper.CurrencyCad)
                    QuotaHotelBData.LgSum = Convert.ToDecimal(QuotaHotelBData.LgSum + hotel.Luggage);
                if (hotel.Currency == StringHelper.CurrencyCad)
                    QuotaHotelBData.LgSumCad = Convert.ToDecimal(QuotaHotelBData.LgSumCad + hotel.Luggage);
                if (hotel.Currency != StringHelper.CurrencyCad)
                    QuotaHotelBData.RppSum = Convert.ToDecimal(QuotaHotelBData.RppSum + hotel.RoomPP);
                if (hotel.Currency == StringHelper.CurrencyCad)
                    QuotaHotelBData.RppSumCad = Convert.ToDecimal(QuotaHotelBData.RppSumCad + hotel.RoomPP);
                if (hotel.Currency != StringHelper.CurrencyCad)
                    QuotaHotelBData.SsSum = Convert.ToDecimal(QuotaHotelBData.SsSum + hotel.SingleSupp);
                if (hotel.Currency == StringHelper.CurrencyCad)
                    QuotaHotelBData.SsSumCad = Convert.ToDecimal(QuotaHotelBData.SsSumCad + hotel.SingleSupp);
            }
            QuotaHotelBData.NightSum = NightSum;
            QuotaHotelBData.ArrivalDate = _context.tblQuotations.Where(quota => quota.QuotaID == QuotaId).SingleOrDefault().ArrivalDate;
        }
        private void InitLunchDinnerGridBData(int QuotaId, QuotaLunchDinnerGridBData QuotaLunchBData)
        {
            QuotaLunchBData.QuotaLunchDinnerConfirmed = _context.tblQuotations.Where(Quota => Quota.QuotaID == QuotaId).Select(Quota => Quota.Accepted).SingleOrDefault();
            QuotaLunchBData.Rows = new List<QuotaLunchDinnerGridRowBData>();
            QuotaLunchBData.Rows = (from qulunch in _context.tblQuotaLunDins
                                    where qulunch.QuotaID == QuotaId
                                    orderby qulunch.LineNo
                                    select new QuotaLunchDinnerGridRowBData()
                                    {
                                        ItmID = qulunch.QuotaLunDinID,
                                        LunchVendorID = qulunch.VendorID,
                                        DinnerVendorID = qulunch.DVendorID,
                                        QuotaID = qulunch.QuotaID,
                                        Cost = qulunch.Cost,
                                        CostD = qulunch.CostD,
                                        Day = qulunch.Day,
                                        Description = qulunch.Description,
                                        DescriptionD = qulunch.DescriptionD,
                                        FreeFlag = qulunch.FreeFlag,
                                        FreeFlagD = qulunch.FreeFlagD,
                                        GuideFlag = qulunch.GuideFlag,
                                        GuideFlagD = qulunch.GuideFlagD,
                                        LunchMFlag = qulunch.LunchMFlag,
                                        DinnerMFlag = qulunch.DinnerMFlag,
                                        // LineDate = qulunch.
                                        //LineDName = qulunch.
                                        LineNo = qulunch.LineNo,
                                        Currency = qulunch.Currency,
                                    }).ToList();
            QuotaLunchBData.ArrivalDate = _context.tblQuotations.Where(quota => quota.QuotaID == QuotaId).SingleOrDefault().ArrivalDate;

            foreach (var lunch in QuotaLunchBData.Rows)
            {
                if (lunch.Cost > 0)
                    QuotaLunchBData.LunchSum++;
                if (lunch.CostD > 0)
                    QuotaLunchBData.DinnerSum++;
                if (lunch.Currency != StringHelper.CurrencyCad)
                    QuotaLunchBData.TotalSum = Convert.ToDecimal(QuotaLunchBData.TotalSum + lunch.Cost + lunch.CostD);
                if (lunch.Currency == StringHelper.CurrencyCad)
                    QuotaLunchBData.TotalSumCad = Convert.ToDecimal(QuotaLunchBData.TotalSumCad + lunch.Cost + lunch.CostD);
                if (lunch.GuideFlag != null && lunch.GuideFlag == true && lunch.Currency != StringHelper.CurrencyCad)
                    QuotaLunchBData.GuideSum = Convert.ToDecimal(QuotaLunchBData.GuideSum + lunch.Cost);
                if (lunch.GuideFlag != null && lunch.GuideFlag == true && lunch.Currency == StringHelper.CurrencyCad)
                    QuotaLunchBData.GuideSumCad = Convert.ToDecimal(QuotaLunchBData.GuideSumCad + lunch.Cost);

                if (lunch.GuideFlagD != null && lunch.GuideFlagD == true && lunch.Currency != StringHelper.CurrencyCad)
                    QuotaLunchBData.GuideSum = Convert.ToDecimal(QuotaLunchBData.GuideSum + lunch.CostD);
                if (lunch.GuideFlagD != null && lunch.GuideFlagD == true && lunch.Currency == StringHelper.CurrencyCad)
                    QuotaLunchBData.GuideSumCad = Convert.ToDecimal(QuotaLunchBData.GuideSumCad + lunch.CostD);

                if (lunch.FreeFlag != null && lunch.FreeFlag == true && lunch.Currency != StringHelper.CurrencyCad)
                    QuotaLunchBData.FreeSum = Convert.ToDecimal(QuotaLunchBData.FreeSum + lunch.Cost);
                if (lunch.FreeFlag != null && lunch.FreeFlag == true && lunch.Currency == StringHelper.CurrencyCad)
                    QuotaLunchBData.FreeSumCad = Convert.ToDecimal(QuotaLunchBData.FreeSumCad + lunch.Cost);
                if (lunch.FreeFlagD != null && lunch.FreeFlagD == true && lunch.Currency != StringHelper.CurrencyCad)
                    QuotaLunchBData.FreeSum = Convert.ToDecimal(QuotaLunchBData.FreeSum + lunch.CostD);
                if (lunch.FreeFlagD != null && lunch.FreeFlagD == true && lunch.Currency == StringHelper.CurrencyCad)
                    QuotaLunchBData.FreeSumCad = Convert.ToDecimal(QuotaLunchBData.FreeSumCad + lunch.CostD);
            }
            QuotaLunchBData.LunchDinnerSum = Convert.ToInt16(QuotaLunchBData.LunchSum + QuotaLunchBData.DinnerSum);
        }
        private void InitEntranceGridBData(int QuotaId, QuotaEntranceGridBData QuotaEntranceBData)
        {
            QuotaEntranceBData.QuotaEntranceConfirmed = _context.tblQuotations.Where(Quota => Quota.QuotaID == QuotaId).Select(Quota => Quota.Accepted).SingleOrDefault();
            QuotaEntranceBData.Rows = new List<QuotaEntranceGridRowBData>();
            QuotaEntranceBData.Rows = (from quentr in _context.tblQuotaEntrances
                                       where quentr.QuotaID == QuotaId 
                                       orderby quentr.LineNo
                                       select new QuotaEntranceGridRowBData()
                                       {
                                           ItmID = quentr.QuotaEntranceID,
                                           VendorID = quentr.VendorID,
                                           QuotaID = quentr.QuotaID,
                                           Cost = quentr.Cost,
                                           CostG = quentr.CostG,
                                           Description = quentr.Description,
                                           FreeFlag = quentr.FreeFlag,
                                           GuideFlag = quentr.GuideFlag,
                                           LineNo = quentr.LineNo,
                                           Day = quentr.Day,
                                           Currency = quentr.Currency,
                                           EntranceMFlag = quentr.EntranceMFlag,
                                       }).ToList();
            QuotaEntranceBData.ArrivalDate = _context.tblQuotations.Where(quota => quota.QuotaID == QuotaId).SingleOrDefault().ArrivalDate;

            foreach (var entrance in QuotaEntranceBData.Rows)
            {
                if(entrance.Cost > 0)
                    QuotaEntranceBData.PPSum++;
                if (entrance.GuideFlag != null && entrance.GuideFlag == true && entrance.Currency != StringHelper.CurrencyCad)
                    QuotaEntranceBData.GuideSum = Convert.ToDecimal(QuotaEntranceBData.GuideSum + entrance.Cost);
                if (entrance.GuideFlag != null && entrance.GuideFlag == true && entrance.Currency == StringHelper.CurrencyCad)
                    QuotaEntranceBData.GuideSumCad = Convert.ToDecimal(QuotaEntranceBData.GuideSumCad + entrance.Cost);

                if (entrance.FreeFlag != null && entrance.FreeFlag == true && entrance.Currency != StringHelper.CurrencyCad)
                {
                    QuotaEntranceBData.FreeSum = Convert.ToDecimal(QuotaEntranceBData.FreeSum + entrance.Cost);
                }
                if (entrance.FreeFlag != null && entrance.FreeFlag == true && entrance.Currency == StringHelper.CurrencyCad)
                {
                    QuotaEntranceBData.FreeSumCad = Convert.ToDecimal(QuotaEntranceBData.FreeSumCad + entrance.Cost);
                }
                if (entrance.CostG != null && entrance.CostG > 0 && entrance.Currency != StringHelper.CurrencyCad)
                {
                    QuotaEntranceBData.TotalPG = Convert.ToDecimal(QuotaEntranceBData.TotalPG + entrance.CostG);
                    QuotaEntranceBData.PGSum++;
                }
                if (entrance.CostG != null && entrance.CostG > 0 && entrance.Currency == StringHelper.CurrencyCad)
                {
                    QuotaEntranceBData.TotalPGCad = Convert.ToDecimal(QuotaEntranceBData.TotalPGCad + entrance.CostG);
                    QuotaEntranceBData.PGSum++;
                }
                if (entrance.Cost != null && entrance.Cost > 0 && entrance.Currency != StringHelper.CurrencyCad)
                    QuotaEntranceBData.TotalPP = Convert.ToDecimal(QuotaEntranceBData.TotalPP + entrance.Cost);
                if (entrance.Cost != null && entrance.Cost > 0 && entrance.Currency == StringHelper.CurrencyCad)
                    QuotaEntranceBData.TotalPPCad = Convert.ToDecimal(QuotaEntranceBData.TotalPPCad + entrance.Cost);
            }
        }
        private void InitMiscGridBData(int QuotaId, QuotaMiscGridBData QuotaMiscBData)
        {
            QuotaMiscBData.QuotaMiscConfirmed = _context.tblQuotations.Where(Quota => Quota.QuotaID == QuotaId).Select(Quota => Quota.Accepted).SingleOrDefault();
            QuotaMiscBData.Rows = new List<QuotaMiscGridRowBData>();
            QuotaMiscBData.Rows = (from qumisc in _context.tblQuotaEntrGrps
                                   where qumisc.QuotaID == QuotaId
                                   orderby qumisc.LineNo
                                   select new QuotaMiscGridRowBData()
                                   {
                                       ItmID = qumisc.QuotaEntrGrpID,
                                       QuotaID = qumisc.QuotaID,
                                       Cost = qumisc.Cost,
                                       CostG = qumisc.CostG,
                                       Description = qumisc.Description,
                                       LineNo = qumisc.LineNo,
                                       Day = qumisc.Day,
                                       Currency = qumisc.Currency,
                                       PlaneFlag = qumisc.PlaneFlag,
                                   }).ToList();
            QuotaMiscBData.ArrivalDate = _context.tblQuotations.Where(quota => quota.QuotaID == QuotaId).SingleOrDefault().ArrivalDate;

            foreach (var misc in QuotaMiscBData.Rows)
            {
                if (misc.Cost != null && misc.Cost > 0 && misc.Currency != StringHelper.CurrencyCad)
                {
                    QuotaMiscBData.TotalPP = Convert.ToDecimal(QuotaMiscBData.TotalPP + misc.Cost);
                    QuotaMiscBData.PPSum++;
                }
                if (misc.Cost != null && misc.Cost > 0 && misc.Currency == StringHelper.CurrencyCad)
                {
                    QuotaMiscBData.TotalPPCad = Convert.ToDecimal(QuotaMiscBData.TotalPPCad + misc.Cost);
                    QuotaMiscBData.PPSum++;
                }
                if (misc.CostG != null && misc.CostG > 0 && misc.Currency != StringHelper.CurrencyCad)
                {
                    QuotaMiscBData.TotalPG = Convert.ToDecimal(QuotaMiscBData.TotalPG + misc.CostG);
                    QuotaMiscBData.PGSum++;
                }
                if (misc.CostG != null && misc.CostG > 0 && misc.Currency == StringHelper.CurrencyCad)
                {
                    QuotaMiscBData.TotalPGCad = Convert.ToDecimal(QuotaMiscBData.TotalPGCad + misc.CostG);
                    QuotaMiscBData.PGSum++;
                }
            }
        }
        private void InitBusGridBData(int QuotaId, QuotaBusGridBData QuotaBusBData)
        {
            QuotaBusBData.QuotaBusConfirmed = _context.tblQuotations.Where(Quota => Quota.QuotaID == QuotaId).Select(Quota => Quota.Accepted).SingleOrDefault();
            QuotaBusBData.Service = (from ser in _context.tblServices
                                     from sertype in _context.tblServiceTypes
                                     where ser.ServiceTypeID == sertype.ServiceTypeID && sertype.VendorTypeID == 3
                                       select new
                                       {
                                           Key = ser.ServiceID,
                                           Value = ser.ServiceCode,
                                       }).ToDictionary(o => o.Key, o => o.Value);
            QuotaBusBData.Rows = new List<QuotaBusGridRowBData>();
            QuotaBusBData.Rows = (from qubus in _context.tblQuotaBus
                                  where qubus.QuotaID == QuotaId
                                  orderby qubus.LineNo
                                  select new QuotaBusGridRowBData()
                                  {
                                      ItmID = qubus.QuotaBusID,
                                      VendorID = qubus.VendorID,
                                      QuotaID = qubus.QuotaID,
                                      Cost = qubus.Cost,
                                      Description = qubus.Description,
                                      Rate = qubus.Rate,
                                      Qty = qubus.Qty,
                                      LineNo = qubus.LineNo,
                                      Day = qubus.Day,
                                      DayQty = qubus.DayQty,
                                      Currency = qubus.Currency,
                                      BusMFlag = qubus.BusMFlag,
                                      ServiceID = qubus.ServiceID,
                                  }).ToList();

            QuotaBusBData.ArrivalDate = _context.tblQuotations.Where(quota => quota.QuotaID == QuotaId).SingleOrDefault().ArrivalDate;
            foreach (var bus in QuotaBusBData.Rows)
            {
                if(bus.Currency != StringHelper.CurrencyCad)
                    QuotaBusBData.TotalSum += Convert.ToDecimal(bus.Cost);
                if (bus.Currency == StringHelper.CurrencyCad)
                    QuotaBusBData.TotalSumCad += Convert.ToDecimal(bus.Cost);
            }

            decimal DriverRoom, DriverRoomCad;
            CalcDriverRoom(QuotaId, out DriverRoom, out DriverRoomCad);
            QuotaBusBData.DriverSum = DriverRoom;
            QuotaBusBData.DriverSumCad = DriverRoomCad;
          //  QuotaBusBData.TotalSum += QuotaBusBData.DriverSum;
        }
        private void InitGuideGridBData(int QuotaId, QuotaGuideGridBData QuotaGuideBData)
        {
            QuotaGuideBData.QuotaGuideConfirmed = _context.tblQuotations.Where(Quota => Quota.QuotaID == QuotaId).Select(Quota => Quota.Accepted).SingleOrDefault();
            QuotaGuideBData.Service = (from ser in _context.tblServices
                                     from sertype in _context.tblServiceTypes
                                     where ser.ServiceTypeID == sertype.ServiceTypeID && sertype.VendorTypeID == 8
                                     select new
                                     {
                                         Key = ser.ServiceID,
                                         Value = ser.ServiceCode,
                                     }).ToDictionary(o => o.Key, o => o.Value);
            QuotaGuideBData.Rows = new List<QuotaGuideGridRowBData>();
            QuotaGuideBData.Rows = (from quguide in _context.tblQuotaGuides
                                    where quguide.QuotaID == QuotaId
                                    orderby quguide.LineNo
                                    select new QuotaGuideGridRowBData()
                                    {
                                        ItmID = quguide.QuotaGuideID,
                                        GuideID = quguide.GuideID,
                                        QuotaID = quguide.QuotaID,
                                        Cost = quguide.Cost,
                                        Description = quguide.Description,
                                        Rate = quguide.Rate,
                                        Qty = quguide.Qty,
                                        LineNo = quguide.LineNo,
                                        Day = quguide.Day,
                                        DayQty = quguide.DayQty,
                                        Currency = quguide.Currency,
                                        GuideMFlag = quguide.GuideMFlag,
                                        ServiceID = quguide.ServiceID,
                                    }).ToList();
            QuotaGuideBData.ArrivalDate = _context.tblQuotations.Where(quota => quota.QuotaID == QuotaId).SingleOrDefault().ArrivalDate;
            decimal? accomExpenses;
            decimal? mealExpenses;
            decimal? entranceExpenses;
            decimal? accomExpensesCad;
            decimal? mealExpensesCad;
            decimal? entranceExpensesCad;
            CalcGuideExpenses(QuotaId, out accomExpenses, out accomExpensesCad, out mealExpenses, out mealExpensesCad, out entranceExpenses, out entranceExpensesCad);
            QuotaGuideBData.RoomLgBk = Convert.ToDecimal(accomExpenses);
            QuotaGuideBData.LunchDinner = Convert.ToDecimal(mealExpenses);
            QuotaGuideBData.Entrance = Convert.ToDecimal(entranceExpenses);
            QuotaGuideBData.RoomLgBkCad = Convert.ToDecimal(accomExpensesCad);
            QuotaGuideBData.LunchDinnerCad = Convert.ToDecimal(mealExpensesCad);
            QuotaGuideBData.EntranceCad = Convert.ToDecimal(entranceExpensesCad);
            foreach (var guide in QuotaGuideBData.Rows)
            {
                if(guide.Currency != StringHelper.CurrencyCad)
                    QuotaGuideBData.SallaryMisc += Convert.ToDecimal(guide.Cost);
                if (guide.Currency == StringHelper.CurrencyCad)
                    QuotaGuideBData.SallaryMiscCad += Convert.ToDecimal(guide.Cost);
            }
            QuotaGuideBData.TotalGuide = QuotaGuideBData.SallaryMisc;
            QuotaGuideBData.TotalGuideCad = QuotaGuideBData.SallaryMiscCad;
            QuotaGuideBData.TotalExpenses = QuotaGuideBData.RoomLgBk + QuotaGuideBData.LunchDinner + QuotaGuideBData.Entrance + QuotaGuideBData.SallaryMisc;
            QuotaGuideBData.TotalExpensesCad = QuotaGuideBData.RoomLgBkCad + QuotaGuideBData.LunchDinnerCad + QuotaGuideBData.EntranceCad + QuotaGuideBData.SallaryMiscCad;

            QuotaGuideBData.Count = Convert.ToInt16(QuotaGuideBData.Rows.Count);

        }
        private void InitCostPriceGridBData(int QuotaId, QuotaCostPriceGridBData QuotaCPriceBData, string FreeCalcType)
        {
            QuotaCPriceBData.QuotaCostPriceConfirmed = _context.tblQuotations.Where(Quotaacc => Quotaacc.QuotaID == QuotaId).Select(Quotaacc => Quotaacc.Accepted).SingleOrDefault();
            QuotaCPriceBData.Rows = new List<QuotaCostPriceGridRowBData>();
            var Quota = _context.tblQuotations.Where(quota => quota.QuotaID == QuotaId).SingleOrDefault();
            if (FreeCalcType == null && Quota != null)
                FreeCalcType = Quota.FreeCalcType.ToString();
            decimal SumFSSp = 0;
            var QuotaHotelList = (from quotahotel in _context.tblQuotaHotels where quotahotel.QuotaID == QuotaId select quotahotel).ToList();
            foreach (var hotel in QuotaHotelList)
            {
                decimal tax = Convert.ToDecimal(hotel.Tax);
                decimal total = Convert.ToDecimal(hotel.Room + (hotel.Room * tax) / 100) + Convert.ToDecimal(hotel.OccTax);
                if(hotel.Currency != StringHelper.CurrencyCad)
                    QuotaCPriceBData.SSupSum += Convert.ToDecimal(hotel.Nights * total / 2);
                if (hotel.Currency == StringHelper.CurrencyCad)
                    QuotaCPriceBData.SSupSumCad += Convert.ToDecimal(hotel.Nights * total / 2);
                SumFSSp = SumFSSp + total / 2 * Convert.ToInt32(hotel.Nights);
            }
            var Prf = _context.tblQuotations.Where(quota => quota.QuotaID == QuotaId).Select(quota => quota.Profit).FirstOrDefault();
            if (Prf != null && Prf > 1)
            {
                QuotaCPriceBData.SSupSum = QuotaCPriceBData.SSupSum + QuotaCPriceBData.SSupSum / 100 * Convert.ToDecimal(Prf);
                QuotaCPriceBData.SSupSumCad = QuotaCPriceBData.SSupSumCad + QuotaCPriceBData.SSupSumCad / 100 * Convert.ToDecimal(Prf);
            }
            if (Prf != null && Prf < 1 && Prf > 0)
            {
                QuotaCPriceBData.SSupSum = QuotaCPriceBData.SSupSum / Convert.ToDecimal(Prf);
                QuotaCPriceBData.SSupSumCad = QuotaCPriceBData.SSupSumCad / Convert.ToDecimal(Prf);
            }
            try
            {
                QuotaCPriceBData.Rows = (from quotacalc in _context.tblQuotaCalcs
                                         where quotacalc.QuotaID == QuotaId
                                         orderby quotacalc.LineNo
                                         select new QuotaCostPriceGridRowBData()
                                         {
                                             QuotaID = quotacalc.QuotaID,
                                             BusPP = quotacalc.BusPP,
                                             EnGPP = quotacalc.EnGPP,
                                             EntPP = quotacalc.EntPP,
                                             PercProfit = quotacalc.PercProfit,
                                             GuiPP = quotacalc.GuiPP,
                                             HtlPP = quotacalc.HtlPP,
                                             LDnPP = quotacalc.LDnPP,
                                             MscGPP = quotacalc.MscGPP,
                                             LineNo = quotacalc.LineNo,
                                             Pax = quotacalc.Pax,
                                             TotalCost = quotacalc.HtlPP + quotacalc.LDnPP + quotacalc.EntPP + quotacalc.EnGPP + quotacalc.MscPP + quotacalc.MscGPP + quotacalc.BusPP + quotacalc.GuiPP,
                                             MM = quotacalc.Manual,
                                             TotalFree = quotacalc.HtlFree + quotacalc.LDnFree + quotacalc.EntFree,
                                             MscPP = quotacalc.MscPP,
                                             PaxL = quotacalc.Pax,
                                             FAdjst = quotacalc.FAdjustment,
                                             Currency = quotacalc.Currency,
                                         }).ToList();
                foreach (var price in QuotaCPriceBData.Rows)
                {
                    decimal? givenfreeNoFree = FreeCalcType == "0" ? 0 : price.TotalCost / price.Pax;
                    decimal givenfreeSingleFree = FreeCalcType == "2" ? SumFSSp / price.Pax : 0;
                    price.FreeCost = price.TotalCost - price.TotalFree;
                    price.Sold = price.FreeCost * (1 + Convert.ToDecimal(price.PercProfit));
                    price.GivenFree = (givenfreeNoFree + givenfreeSingleFree) * Convert.ToDecimal(1 + price.PercProfit);
                    price.GivenFreeP = (givenfreeNoFree + givenfreeSingleFree) * Convert.ToDecimal(price.PercProfit);
                    price.GivenSold = price.Sold + price.GivenFree;
                    price.FPrice = price.GivenSold + (price.FAdjst != null ? price.FAdjst : 0);
                    price.Profit = (price.FPrice - price.GivenFree) - price.FreeCost + price.GivenFreeP;
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            QuotaCPriceBData.SSupAdjs = Quota.SSupAdj;
            QuotaCPriceBData.SSupAdjsCad = Quota.SSupAdjCad;
            QuotaCPriceBData.SSupSumFinal = QuotaCPriceBData.SSupSum + (QuotaCPriceBData.SSupAdjs != null ? Convert.ToDecimal(QuotaCPriceBData.SSupAdjs) : 0);
            QuotaCPriceBData.SSupSumCadFinal = QuotaCPriceBData.SSupSumCad + (QuotaCPriceBData.SSupAdjsCad != null ? Convert.ToDecimal(QuotaCPriceBData.SSupAdjsCad) : 0);
        }
        private string GetExceptionMessages(Exception e, string msgs = "")
        {
            if (e == null) return string.Empty;
            if (msgs == "") msgs = e.Message;
            if (e.InnerException != null)
                msgs += "\r\nInnerException: " + GetExceptionMessages(e.InnerException);
            return msgs;
        }
        #endregion
        public void Dispose()
        {
            _context.Dispose();
        }
    }
}
